[ACCEPTED]-How to get Return Value of a Stored Procedure-ado.net

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

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

This 5 means that if you have a procedure defined 4 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 3 contain null. To make it work, you have 2 to execute the procedure like:

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 1 case, x will contain 1 as expected.

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

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

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 2 results will appear in the immediate window 1 when running this (Debug.Print)

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

Several ways are possible to get values 5 back using VBA:

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

My code demonstrates all 4 four. Here is a stored procedure that returns 3 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 2 need reference to Microsoft ActiveX Data 1 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

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

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