How to return the output of stored procedure into a variable in sql server

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.

more on RETURN statement

That depends on the nature of the information you want to return.

If it is a single integer value, you can use the return statement

 create proc myproc
as
begin
return 1
end
go
declare @i int
exec @i = myproc

If you have a non integer value, or a number of scalar values, you can use output parameters

create proc myproc
@a int output,
@b varchar(50) output
as
begin
select @a = 1, @b='hello'
end
go
declare @i int, @j varchar(50)
exec myproc @i output, @j output

If you want to return a dataset, you can use insert exec

create proc myproc
as
begin
select name from sysobjects
end
go


declare @t table (name varchar(100))
insert @t (name)
exec myproc

You can even return a cursor but that's just horrid so I shan't give an example :)

Use this code, Working properly

CREATE PROCEDURE [dbo].[sp_delete_item]
@ItemId int = 0
@status bit OUT


AS
Begin
DECLARE @cnt int;
DECLARE @status int =0;
SET NOCOUNT OFF
SELECT @cnt =COUNT(Id) from ItemTransaction where ItemId = @ItemId
if(@cnt = 1)
Begin
return @status;
End
else
Begin
SET @status =1;
return @status;
End
END

Execute SP

DECLARE @statuss bit;
EXECUTE  [dbo].[sp_delete_item] 6, @statuss output;
PRINT @statuss;

With the Return statement from the proc, I needed to assign the temp variable and pass it to another stored procedure. The value was getting assigned fine but when passing it as a parameter, it lost the value. I had to create a temp table and set the variable from the table (SQL 2008)

From this:
declare @anID int
exec @anID = dbo.StoredProc_Fetch @ID, @anotherID, @finalID
exec dbo.ADifferentStoredProc @anID (no value here)


To this:
declare @t table(id int)
declare @anID int
insert into @t exec dbo.StoredProc_Fetch @ID, @anotherID, @finalID
set @anID= (select Top 1 * from @t)