SQL 如何获取存储过程的返回值

How to get Return Value of a Stored Procedure

提问by core

Probably an easy-to-answer question. I have this procedure:

大概是个容易回答的问题。我有这个程序：

CREATE PROCEDURE [dbo].[AccountExists]
    @UserName nvarchar(16)
AS
IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName)
SELECT 1
ELSE SELECT 0 

When I have ADO.NET code that calls this procedure and does this:

当我有调用此过程的 ADO.NET 代码并执行以下操作时：

return Convert.ToBoolean(sproc.ExecuteScalar());

Either true or false is returned.

返回 true 或 false。

When I change the stored procedure to RETURN 1 or 0 instead of SELECT:

当我将存储过程更改为 RETURN 1 或 0 而不是 SELECT 时：

ALTER PROCEDURE [dbo].[AccountExists]
    @UserName nvarchar(16)
AS
IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName)
RETURN 1
ELSE RETURN 0 

sproc.ExecuteScalar() returns null. If I try sproc.ExecuteNonQuery() instead, -1 is returned.

sproc.ExecuteScalar() 返回 null。如果我尝试 sproc.ExecuteNonQuery() ，则返回 -1。

How do I get the result of a stored procedure with a RETURN in ADO.NET?

如何在 ADO.NET 中使用 RETURN 获取存储过程的结果？

I need AccountExists to RETURN instead of SELECT so I can have another stored procedure call it:

我需要 AccountExists 来 RETURN 而不是 SELECT 所以我可以有另一个存储过程调用它：

--another procedure to insert or update account

DECLARE @exists bit

EXEC @exists = [dbo].[AccountExists] @UserName 

IF @exists=1
--update account
ELSE
 --insert acocunt

回答by John Saunders

Add a parameter, using ParameterDirection.ReturnValue. The return value will be present in the paramter after the execution.

添加参数，使用ParameterDirection.ReturnValue. 执行后返回值将出现在参数中。

回答by erikkallen

Also, to retrieve the result (or any other output parameter for that matter) from ADO.NET you have to loop through all returned result sets first (or skip them with NextResult)

此外，要从 ADO.NET 检索结果（或与此相关的任何其他输出参数），您必须首先遍历所有返回的结果集（或使用 NextResult 跳过它们）

This means that if you have a procedure defined like this:

这意味着，如果您有一个这样定义的过程：

CREATE PROC Test(@x INT OUT) AS
    SELECT * From TestTable
    SELECT @x = 1

And try to do this:

并尝试这样做：

SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test"
cmd.Parameters.Add("@x", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@retval", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

cmd.Execute();
int? x = cmd.Parameters["@x"].Value is DBNull ? null : (int?)cmd.Parameters["@x"].Value;

Then x will contain null. To make it work, you have to execute the procedure like:

那么 x 将包含空值。要使其工作，您必须执行如下程序：

using (var rdr = cmd.ExecuteReader()) {
    while (rdr.Read())
        MaybeDoSomething;
}
int? x = cmd.Parameters["@x"].Value is DBNull ? null : (int?)cmd.Parameters["@x"].Value;

In the latter case, x will contain 1 as expected.

在后一种情况下，x 将按预期包含 1。

回答by Darren Kopp

ExecuteScalar returns the first column of the first row. Since you were no longer selecting, and creating a resultset, that is why it was returning null. Just as FYI. John Saunders has the correct answer.

ExecuteScalar 返回第一行的第一列。由于您不再选择和创建结果集，这就是它返回 null 的原因。就像仅供参考。约翰桑德斯有正确的答案。

回答by Pecos Bill

I tried the other solutions with my setup and they did not work but I'm using VB6 & ADO 6.x. I also want to point out that a proc return of 0 indicates successful. Don't forget there are functions available too which don't have that convention. Found this on MSDN and it did work for me:

我在我的设置中尝试了其他解决方案，但它们不起作用，但我使用的是 VB6 和 ADO 6.x。我还想指出 proc 返回 0 表示成功。不要忘记也有一些没有这种约定的可用函数。在 MSDN 上找到了这个，它确实对我有用：

Debug.Print "starting at ..." & TimeValue(Now)

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
'These are two possible connection strings. You could also have Integrated Security instead of these for SqS for security
'cn.ConnectionString = "Data Source=[yourserver];User ID=[youruser];Password=[yourpw];Initial Catalog=[yourdb];Provider=SQLNCLI10.1;Application Name=[yourapp]"
cn.ConnectionString = "Data Source=[yours];User ID=[youruser];Password=[yourpassword];Initial Catalog=[Yourdb];Provider=sqloledb;Application Name=[yourapp]"
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = "AccountExists"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("UserName",adVarChar, adParamInput, 16, UserNameInVB)

cmd.Execute
Debug.Print "Returnval: " & cmd.Parameters(0)
cn.Close

Set cmd = Nothing
Set cn = Nothing

Debug.Print "finished at ..." & TimeValue(Now)

The results will appear in the immediate window when running this (Debug.Print)

运行此（Debug.Print）时，结果将显示在即时窗口中

回答by D_Bester

Several ways are possible to get values back using VBA:

有几种方法可以使用 VBA 取回值：

1. Recordset
2. Count of records affected (only for Insert/Update/Delete otherwise -1)
3. Output parameter
4. Return value

1. 记录集
2. 受影响的记录数（仅适用于插入/更新/删除，否则为 -1）
3. 输出参数
4. 返回值

My code demonstrates all four. Here is a stored procedure that returns a value:

我的代码演示了所有四个。这是一个返回值的存储过程：

Create PROCEDURE CheckExpedite
    @InputX  varchar(10),
    @InputY int,
    @HasExpedite int out
AS
BEGIN
    Select @HasExpedite = 9 from <Table>
    where Column2 = @InputX and Column3 = @InputY

    If @HasExpedite = 9
        Return 2
    Else
        Return 3
End

Here is the sub I use in Excel VBA. You'll need reference to Microsoft ActiveX Data Objects 2.8 Library.

这是我在 Excel VBA 中使用的子程序。您需要参考 Microsoft ActiveX Data Objects 2.8 Library。

Sub CheckValue()

    Dim InputX As String: InputX = "6000"
    Dim InputY As Integer: InputY = 2014

    'open connnection
    Dim ACon As New Connection
    ACon.Open ("Provider=SQLOLEDB;Data Source=<SqlServer>;" & _
        "Initial Catalog=<Table>;Integrated Security=SSPI")

    'set command
    Dim ACmd As New Command
    Set ACmd.ActiveConnection = ACon
    ACmd.CommandText = "CheckExpedite"
    ACmd.CommandType = adCmdStoredProc

    'Return value must be first parameter else you'll get error from too many parameters
    'Procedure or function "Name" has too many arguments specified.
    ACmd.Parameters.Append ACmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
    ACmd.Parameters.Append ACmd.CreateParameter("InputX", adVarChar, adParamInput, 10, InputX)
    ACmd.Parameters.Append ACmd.CreateParameter("InputY", adInteger, adParamInput, 6, InputY)
    ACmd.Parameters.Append ACmd.CreateParameter("HasExpedite", adInteger, adParamOutput)

    Dim RS As Recordset
    Dim RecordsAffected As Long

    'execute query that returns value
    Call ACmd.Execute(RecordsAffected:=RecordsAffected, Options:=adExecuteNoRecords)

    'execute query that returns recordset
    'Set RS = ACmd.Execute(RecordsAffected:=RecordsAffected)

    'get records affected, return value and output parameter
    Debug.Print "Records affected: " & RecordsAffected
    Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")
    Debug.Print "Output param: " & ACmd.Parameters("HasExpedite")

    'use record set here
    '...

    'close
    If Not RS Is Nothing Then RS.Close
    ACon.Close

End Sub

回答by Russ Cam

Just some advice, but by default, a Stored Procedure returns 0 unless you specify something else. For this reason, 0 is often used to designate success and non-zero values are used to specify return error conditions. I would go with John's suggestion, or use an output parameter

只是一些建议，但默认情况下，除非您指定其他内容，否则存储过程返回 0。出于这个原因，0 通常用于指定成功，非零值用于指定返回错误条件。我会接受约翰的建议，或者使用output parameter

回答by Luke Lowrey

If you are planing on using it like the example below AccountExists might be better off as a function.

如果您打算像下面的示例一样使用它 AccountExists 作为函数可能会更好。

Otherwise you should still be able to get the result of the stored procedure by calling it from another one by doing a select on the result.

否则，您仍然应该能够通过对结果进行选择来从另一个存储过程调用它来获取存储过程的结果。