Getting return value from stored procedure in C#

sql

stored-procedures

best-practices

error-handling

byAlex Kataev·Oct 17, 2024

To interact with a stored procedure and retrieve a return value, we use SqlCommand object in C#, set its CommandType to StoredProcedure, and use a SqlParameter to hold the return value. Here’s how you get this done:

using (var conn = new SqlConnection("your_connection_string"))
{
    conn.Open();
    using (var cmd = new SqlCommand("your_stored_procedure_name", conn) { CommandType = CommandType.StoredProcedure })
    {
        var retValParam = cmd.Parameters.Add("@Return", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
        cmd.ExecuteNonQuery();
        // Time to unveil the magic trick
        int returnValue = (int)retValParam.Value; 
    }
}

Make sure to replace "your_connection_string" and "your_stored_procedure_name" with your own values. After ExecuteNonQuery, returnValue holds your sought-after number.

Bulletproof your code: best practices

The command object

Ensure your SqlCommand is correctly configured to interact with stored procedures. Set CommandType to CommandType.StoredProcedure.

Data types

Maintain data type consistency to avoid conversion errors, ensure SqlParameter data types match with stored procedure's expected data type.

Errors handling

Embrace try-catch blocks to handle potential errors and ensure your app's reliability.

Dealing with complex types

If the stored procedure returns complex types or strings, considering using OUTPUT parameters. Distinguish between return values and output parameters to ensure accuracy.

Navigating edge cases

The SELECT decoy

SELECT and RETURN may look interchangeable but cause confusion, especially if SELECT is used to return a value. Stick with parameter values to fetch return values.