How to get Return Value of a Stored Procedure
Solution 1
Add a parameter, using ParameterDirection.ReturnValue
. The return value will be present in the paramter after the execution.
Solution 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)
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:
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.
Solution 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.
Solution 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:
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)
Solution 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
![Dimon Buzz](https://i.stack.imgur.com/MimXy.jpg?s=256&g=1)
Dimon Buzz
Updated on October 06, 2020Comments
-
Dimon Buzz over 3 years
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.
When I change the stored procedure to RETURN 1 or 0 instead of 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.
How do I get the result of a stored procedure with a RETURN in ADO.NET?
I need AccountExists to RETURN instead of SELECT so I can have another stored procedure call it:
--another procedure to insert or update account DECLARE @exists bit EXEC @exists = [dbo].[AccountExists] @UserName IF @exists=1 --update account ELSE --insert acocunt
-
John Saunders about 15 years@erikkallen: you might want to clarify that you mean it's necessary to loop through all result sets that involved calls to stored procedures with output or return value parameters.
-
D_Bester almost 9 yearsThis wasn't working for me in VBA until I found out that the ReturnValue must be the first parameter. stackoverflow.com/a/25528645/2559297