Stored procedure returns null as output parameter

Question

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

What's the procedure look like? Are you running it in the same database in VS and in SSMS? — Oded
– Oded, Commented Sep 15, 2013 at 8:23
By the way you should be testing against DBNull.Value, not null. — Oded
– Oded, Commented Sep 15, 2013 at 8:25
Check your connection strings again. I suspect you are on different databases (possibly on the same server) — Oded
– Oded, Commented Sep 15, 2013 at 8:29

SpoksST · Accepted Answer · 2013-09-15 09:18:53Z

27

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.

answered Sep 15, 2013 at 9:18

SpoksST

8261 gold badge10 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SpoksST Over a year ago

@Oded solution was simple :(

Sturla Over a year ago

Or if you like one-liners you could do something like var SomeVal = command.Parameters["@p_SomeVal"].Value != null ? (int)command.Parameters["@p_SomeVal"].Value : default(int);

Sturla Over a year ago

Sorry I was too hasty... null and DBNull... you will have to use this here var SomeVal = command.Parameters["@p_SomeVal"].Value is DBNull ? default(int) : (int)command.Parameters["@p_SomeVal"].Value;

marc_s · Accepted Answer · 2019-11-12 21:47:29Z

4

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.

edited Nov 12, 2019 at 21:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 29, 2019 at 6:10

Ali Jamal

1,5891 gold badge16 silver badges21 bronze badges

Collectives™ on Stack Overflow

Stored procedure returns null as output parameter

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related