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

Retrieving Return Value from Stored Procedure in C#

When executing a stored procedure that returns a value in C#, the logical error that often leads to a null return is the omission of executing the query.

To execute the stored procedure and retrieve the return value, you need to utilize ExecuteNonQuery() method after adding the necessary parameters. The following code snippet demonstrates the corrected version of the code:

SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);

string returnValue = string.Empty;

try
{
    SqlConn.Open();
    sqlcomm.CommandType = CommandType.StoredProcedure;

    SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
    param.Direction = ParameterDirection.Input;
    param.Value = Username;
    sqlcomm.Parameters.Add(param);



    SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
    retval.Direction = ParameterDirection.ReturnValue;

    // Execute the stored procedure
    sqlcomm.ExecuteNonQuery();

    string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
}
catch(Exception ex)
{
    // Handle any exceptions here
}

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