How can I get Stored Procedure return value in C# ?

Question

I have a stored Procedure that do some operation and return 1 or 0 as below

CREATE PROCEDURE dbo.UserCheckUsername11
(
    @Username nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;

IF Exists(SELECT UserID FROM User WHERE username =@Username) 
    return 1
ELSE
    return 0
END

and in C# i want to get this returned value i try ExcuteScalar but it didn't return any value ( i know if i replace return 0 with Select 0 ExcuteScalar will catach it .. but is it any way that allow me to get returned value with replace return with select ?

NOTE : I don't want to user stored procedure output parameter also

Answer 1

When adding parameters, there is a "return value" parameter direction:

var param = cmd.Parameters.Add(cmd.CreateParameter());
param.Direction = System.Data.ParameterDirection.ReturnValue;

Inspect the .Value of this param after the call (ExecuteNonQuery), and you have the return value. One note, though; when using this with SELECT results, the return value is only available after all the data has been read from the results (return/output is at the end of the TDS stream).

Answer 2

In essence you have to define a parameter with "direction" return value.

You can find some examples here: Using ADO.NET with SQL Server and here: Get the Return Value from an SQL Stored Procedure.

Here comes the relevant part from the first link cited:

To get the value of a RETURN statement, you need to do the following:

// add a new parameter, with any name we want - its for our own
// use only
SqlParameter sqlParam = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.ReturnValue;

Then, after the stored procedure has been executed,

int returnValue = (int)com.Parameters["@ReturnValue"].Value

will retrieve the value that was set.

Answer 3

You need to add a parameter with a direction = ReturnValue:

using (var command = new SqlCommand("dbo.CheckUsername11", conn) 
            { CommandType = CommandType.StoredProcedure })
{
  command.Parameters.Add(new SqlParameter("@result") { ParameterDirection.ReturnValue });
  command.Parameters.AddWithValue("@Username", username);

  command.ExecuteNonQuery();

  return (int)command.Parameters["@result"].Value;
}

Answer 4

Check the following URLs it contain the same problem
1- http://stackoverflow.com/questions/749622/ado-net-how-to-get-return-value-of-a-stored-procedure
2- http://stackoverflow.com/questions/2342806/getting-a-return-value-in-c-asp-net-from-a-stored-procedure-syntax-issue