December 24th, 2008 
admin Recently one of my friend asked me to review his code to see why he was not able to get the return value from stored procedure.
With the stored procedure, he was returning 1 in the stored procedure if the operation doesn’t succeed. He was executing the command via IDBCommand.ExecuteNonQuery() and then check the return value to see how many records were affected. Apparently ExecuteNonQuery only returns the number of affected rows on SELECT, INSERT and DELETE statements; for everything else it returns -1. So, here is what I suggested him to get a return value from a stored procedure.
Let’s assume a simplistic stored procedure as follows:
|
ALTER PROC usp_TestReturnOnly
AS
BEGIN
RETURN 1
END |
You can’t use ExecuteScalar to get the returned value, and ExecuteNonQuery will always return -1. To get the value back, you need to add a return value parameter to the command. The name of the parameter is not important. The C# data access code to get the value returned by that procedure will be as follows:
|
private int ExecuteStoredProcedure(string strStoredProcedureName)
{
SqlConnection sCon= new SqlConnection(strConnectionString);
// Command – specify as StoredProcedure
SqlCommand sCmd= new SqlCommand(strStoredProcedureName, sCon);
sCmd.CommandType = CommandType.StoredProcedure;
// Return value as parameter
SqlParameter spReturnValue = new SqlParameter("returnValue", SqlDbType.Int);
spReturnValue .Direction = ParameterDirection.ReturnValue;
sCmd.Parameters.Add(spReturnValue );
// Execute the stored procedure
sCon.Open();
sCmd.ExecuteNonQuery();
sCon.Close();
return Convert.ToInt32(spReturnValue .Value);
}
|
Popularity: 2% [?]
Posted in 
Tags: 