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

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:

当我有麻烦。调用此过程并执行以下操作的网络代码:

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:

当我将存储过程更改为返回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中获得存储过程的结果?

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

我需要AccountExists来返回而不是SELECT，以便让另一个存储过程调用它:

--another procedure to insert or update account

DECLARE @exists bit

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

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

7 个解决方案

#1

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

使用parameters . returnvalue添加一个参数。执行之后，返回值将出现在paramter中。

#2

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将包含null。要使其工作，您必须执行以下步骤:

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。

#3

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返回第一行的第一列。因为您不再选择并创建一个resultset，所以返回null。仅供参考。约翰·桑德斯的答案是正确的。

#4

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)

#5

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来指定成功，使用非0值来指定返回错误条件。我会按照John的建议，或者使用一个输出参数

#6

Several ways are possible to get values back using VBA:

使用VBA可以通过几种方式获得值:

1. Recordset
2. 记录集
3. Count of records affected (only for Insert/Update/Delete otherwise -1)
4. 受影响的记录计数(仅用于插入/更新/删除，否则为-1)
5. Output parameter
6. 输出参数
7. Return value
8. 返回值

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中使用的sub。您需要参考Microsoft ActiveX数据对象2.8库。

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

#7

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.

否则，您仍然可以通过对结果执行select来从另一个过程调用存储过程，从而获得存储过程的结果。