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.
Return data using an output parameter. If you specify the output keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits.
Return Value in SQL Server Stored ProcedureIn default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
Return Value in Stored Procedure. Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure will return 0.
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; }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With