How to Retrieve a Stored Procedure's Return Value in C#?

Get stored procedure return value in C#

This article describes how to call a stored procedure from a C# Windows application and get its return value. Stored procedure usp_GetNewSeqVal is used to generate new sequence values. Despite referring to some solutions on the Internet, you still cannot successfully obtain the return value.

The problem is that the ReturnValue direction parameter is missing from the command. To fix this you need to add the following parameters:

<code class="language-csharp">var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;</code>

Set the direction of the parameter to ParameterDirection.ReturnValue instructs SqlCommand to declare it as a variable and assign the return value of the stored procedure to it. This is similar to the syntax used in SQL (exec @ReturnValue = spMyProcedure...).

By adding this parameter, the cmd.ExecuteReader() call will execute the stored procedure and store the return value in returnParameter. You can then access it as returnParameter.Value.

Here is the code with the necessary modifications:

<code class="language-csharp">using (SqlConnection conn = new SqlConnection(getConnectionString()))
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = parameterStatement.getQuery();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("SeqName", "SeqNameValue");

    var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    conn.Open();
    cmd.ExecuteNonQuery();
    var result = returnParameter.Value;
}</code>

By making this change, you will be able to successfully retrieve the return value of the stored procedure.

The above is the detailed content of How to Retrieve a Stored Procedure's Return Value in C#?. For more information, please follow other related articles on the PHP Chinese website!