I have stored procedure
, which works great in MS SQL management studio.
When I try to use it in VS rows returns fine, but value of output parameters is NULL.
SqlCommand cmd = new SqlCommand("proc_name", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p_SomeVal", SqlDbType.Int));
cmd.Parameters["@p_SomeVal"].Direction = ParameterDirection.Output;
rdr = cmd.ExecuteReader();
//...process rows...
if (cmd.Parameters["@p_SomeVal"].Value != null)
SomeVal = (int)cmd.Parameters["@p_SomeVal"].Value;
cmd.ExecuteNonQuery(); Has the same result.
USE [db_name]
GO
DECLARE @return_value int,
@p_SomeValue int
EXEC @return_value = [dbo].[Proc_name]
@p_InputVal = N'aa',
@p_SomeValue = @p_SomeValue OUTPUT
SELECT @p_SomeValue as N'p_SomeValue'
SELECT 'Return Value' = @return_value
GO
If you try to return NULL from a stored procedure using the RETURN keyword, you will get a warning, and 0 is returned instead. If a procedure hits an error that requires it to terminate immediately, it will return NULL because it never gets to either the RETURN keyword or the end of the batch!
The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.
A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.
The simplest kind of SQL Server stored procedure that you can call is one that contains no parameters and returns a single result set. The Microsoft JDBC Driver for SQL Server provides the SQLServerStatement class, which you can use to call this kind of stored procedure and process the data that it returns.
SqlCommand cmd = new SqlCommand("proc_name", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p_SomeVal", SqlDbType.Int));
cmd.Parameters["@p_SomeVal"].Direction = ParameterDirection.Output;
rdr = cmd.ExecuteReader();
//...process rows...
rdr.Close();
if (cmd.Parameters["@p_SomeVal"].Value != null)
SomeVal = (int)cmd.Parameters["@p_SomeVal"].Value;
After procesing rows I added rdr.Close();
and worked fine.
Salaam, You can check if output is null and convert like this.
returnedSQLParameter.Value != DBNull.Value? (int)returnedSQLParameter.Value : 0;
Because it is returning DBNull.value
when output sent NULL
from stored procedure.
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