How to Retrieve a Return Value from a SQL Server Stored Procedure in C#?

Accessing Return Values from SQL Server Stored Procedures in C#

This article explains how to correctly retrieve return values from SQL Server stored procedures within a C# application. A common problem arises when trying to access a return value—for example, the next sequence number—without proper parameter handling.

The example uses a stored procedure, usp_GetNewSeqVal, which increments a sequence value in the AllSequences table and returns the updated value. The C# code connects to the database and executes the stored procedure using SqlCommand.

The key to retrieving the return value is adding a parameter with ParameterDirection.ReturnValue to the SqlCommand's parameters collection. This dedicated parameter acts as a container for the stored procedure's output.

Here's the corrected C# code snippet:

using (SqlCommand cmd = conn.CreateCommand())
{
    // ... other code ...

    // Add a parameter to receive the return value
    SqlParameter returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    // ... other code ...

    conn.Open();
    cmd.ExecuteNonQuery();
    int result = (int)returnParameter.Value; // Cast to appropriate type
}

By setting ParameterDirection to ReturnValue, the SqlParameter is designated as the recipient of the stored procedure's return value. After ExecuteNonQuery(), the Value property of this parameter holds the returned value, which can then be accessed and used by the C# application. Remember to cast the Value property to the correct data type.

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