Calling stored procedure with return value
Solution 1
You need to add return parameter to the command:
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;
}
Solution 2
I know this is old, but i stumbled on it with Google.
If you have a return value in your stored procedure say "Return 1" - not using output parameters.
You can do the following - "@RETURN_VALUE" is silently added to every command object. NO NEED TO EXPLICITLY ADD
cmd.ExecuteNonQuery();
rtn = (int)cmd.Parameters["@RETURN_VALUE"].Value;
Solution 3
The version of EnterpriseLibrary on my machine had other parameters. This was working:
SqlParameter retval = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
retval.Direction = System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add(retval);
db.ExecuteNonQuery(cmd);
object o = cmd.Parameters["@ReturnValue"].Value;
Solution 4
This is a very short sample of returning a single value from a procedure:
SQL:
CREATE PROCEDURE [dbo].[MakeDouble] @InpVal int AS BEGIN
SELECT @InpVal * 2; RETURN 0;
END
C#-code:
int inpVal = 11;
string retVal = "?";
using (var sqlCon = new SqlConnection(
"Data Source = . ; Initial Catalog = SampleDb; Integrated Security = True;"))
{
sqlCon.Open();
retVal = new SqlCommand("Exec dbo.MakeDouble " + inpVal + ";",
sqlCon).ExecuteScalar().ToString();
sqlCon.Close();
}
Debug.Print(inpVal + " * 2 = " + retVal);
//> 11 * 2 = 22
Solution 5
I had a similar problem with the SP call returning an error that an expected parameter was not included. My code was as follows.
Stored Procedure:
@Result int OUTPUT
And C#:
SqlParameter result = cmd.Parameters.Add(new SqlParameter("@Result", DbType.Int32)); result.Direction = ParameterDirection.ReturnValue;
In troubleshooting, I realized that the stored procedure was ACTUALLY looking for a direction of "InputOutput" so the following change fixed the problem.
r
Result.Direction = ParameterDirection.InputOutput;
Comments
-
Wesley almost 2 years
I am trying to call a stored procedure from my C# windows application. The stored procedure is running on a local instance of SQL Server 2008. I am able to call the stored procedure but I am not able to retrieve the value back from the stored procedure. This stored procedure is supposed to return the next number in the sequence. I have done research online and all the sites I've seen have pointed to this solution working.
Stored procedure code:
ALTER procedure [dbo].[usp_GetNewSeqVal] @SeqName nvarchar(255) as begin declare @NewSeqVal int set NOCOUNT ON update AllSequences set @NewSeqVal = CurrVal = CurrVal+Incr where SeqName = @SeqName if @@rowcount = 0 begin print 'Sequence does not exist' return end return @NewSeqVal end
Code calling the stored procedure:
SqlConnection conn = new SqlConnection(getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand(parameterStatement.getQuery(), conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter(); param = cmd.Parameters.Add("@SeqName", SqlDbType.NVarChar); param.Direction = ParameterDirection.Input; param.Value = "SeqName"; SqlDataReader reader = cmd.ExecuteReader();
I have also tried using a
DataSet
to retrieve the return value with the same result. What am I missing to get the return value from my stored procedure? If more information is needed, please let me know. -
muttley91 over 10 yearsDo you have to add
@ReturnVal
to the Stored Procedure as well? -
Michael over 9 yearsHi Gary. Do you happen to have a citation?
-
ufosnowcat over 9 yearsNo, you don't have to add to the sp (the return statement triggers filling the parameter marked with ReturnValue)
-
Soori about 8 yearsExecuteScalar() doesn't work, unless you "select" the return value.
-
Clay about 8 yearsDoesn't matter what you name the return val parameter, btw.
-
jb007 over 7 yearsI dont believe that the cmd instance will have a parameter named in RETURN_VALUE in the SqlParameterCollection unless it is added explicitly. Therefore, if you are considering taking the approach above by Gary, then ensure you have added cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int)); cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; However, I do believe that you DO NOT have to add the @RETURN_VALUE to the stored procedure definition. Perhaps that is what Gary meant.
-
Oswaldo Zapata over 6 yearsthanks a lot, I was looking for a way to properly get return value, this really worked for me. awesome!
-
tony09uk over 6 yearsLooking through SqlDbType enumeration, all the types are for returning only a single value. How do you define that a table of data is being returned?
-
Manuel Hoffmann over 6 years@tony09uk I don't think ReturnValue supports that. But you can simply not use return in your procedure and instead end the procedure with
SELECT * FROM tableToReturn
. In your programm, use anSqlDataAdapter
to get the table from the select statement in your stored procedure just like you would get get the result of a normal SQL statement. -
Dan Guzman almost 6 yearsThe stored procedure return value should be used only to indicate success (zero) or failure/warning (non-zero), not return data. Use an
OUTPUT
parameter or result set for that purpose. -
Dai over 3 years"Output parameters" are not the same thing as Stored Procedure return-values, btw.