CODEWITHSUNDEEP
c#sqlstored-procedures
SpoksST
SpoksST

Reputation: 806

Stored procedure returns null as output parameter

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

Upvotes: 10

Views: 22392

Answers (2)

Ali Jamal
Ali Jamal

Reputation: 1549

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.

Upvotes: 4

SpoksST
SpoksST

Reputation: 806

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.

Upvotes: 26

Related Questions