You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success.
If no return is explicitly set, then the stored procedure returns zero.
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
if @@rowcount = 0 -- manager not found?
return 1;
END
And you call it this way:
DECLARE @return_status int;
DECLARE @managerID int;
EXEC @return_status = GetImmediateManager 2, @managerID output;
if @return_status = 1
print N'Immediate manager not found!';
else
print N'ManagerID is ' + @managerID;
go
You should use the return value for status codes only. To return data, you should use output parameters.
If you want to return a dataset, then use an output parameter of type cursor.