How to return a value from the SQL Stored procedure

Que: How to return a value from the SQL Stored procedure.

Ans: With the out parameter we can return the value from the SQL Server.

Below is example stored procedure created in SQL Server 2005 AdventureWorks sample database.

CREATE PROCEDURE GetImmediateManager

   @employeeID INT,

   @managerID INT OUTPUT

AS

BEGIN

   SELECT @managerID = ManagerID

   FROM HumanResources.Employee

   WHERE EmployeeID = @employeeID

END

Value of Out parameter @managerid we can get in C#.

Now If I want to return the value in SQL from the stored procedure then we need to follow the below approach.

Below are the example stored procedure.

CREATE PROCEDURE TestSP1

AS

BEGIN

      SELECT 1

END

GO

CREATE PROCEDURE TestSP2

AS

BEGIN

      DECLARE @Temp INT

      EXEC @Temp = TestSP1

END

GO

EXEC TestSP2

Thanks,
RS