如何将存储过程的输出返回到sql server中的一个变量中

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

我想在SQL Server中执行一个存储过程并将输出分配给一个变量(它返回一个值)?

4 个解决方案

#1

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

如果是单个整数值，则可以使用return语句。

 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

如果要返回数据集，可以使用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 :)

你甚至可以返回一个光标，但那只是可怕的，所以我不会举一个例子:

#2

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.

您可以在存储过程中使用return语句返回一个整数状态码(并且只返回整数类型)。按照惯例，0的返回值用于成功。

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.

如果要返回数据集，则使用类型为游标的输出参数。

#3

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

执行SP

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

#4

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)

通过proc的返回语句，我需要分配temp变量并将其传递给另一个存储过程。这个值得到了很好的分配，但是当它作为参数传递时，它丢失了这个值。我必须创建一个临时表并从表中设置变量(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)

#1