Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calling stored procedure with return value

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.

like image 522
Wesley Avatar asked Jun 02 '11 03:06

Wesley


People also ask

Can stored procedure return value to a caller?

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.

Can SQL stored procedure return value?

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.

Can we return values from procedure?

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.


1 Answers

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; } 
like image 152
Alex Aza Avatar answered Oct 07 '22 00:10

Alex Aza