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

Question

I want to execute a stored procedure in SQL Server and assign the output to a variable  it returns a single value

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to variables

Examples related to stored-procedures