Stored procedure returns null as output parameter
18,795
Solution 1
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.
Solution 2
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.
Author by
SpoksST
Updated on July 22, 2022Comments
-
SpoksST almost 2 years
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
-
SpoksST almost 11 years@Oded solution was simple :(
-
Sturla over 7 yearsOr 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 7 yearsSorry 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;