How to get Return Value of a Stored Procedure

Question

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 

Answer 1

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

Answer 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.