Stored procedure parameter output value

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

I'm calling a stored procedure which has an output parameter of type int.
Once the stored procedure is executed, I want to check the value of the
parameter in case it is null. However, when the a null value is returned I
don't seem to be able to detect it.

Any help would be greatly appreciated.

C# code is as follows:

SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add("@iApplicationID",
SqlDbType.Int);
param.Direction = ParameterDirection.Output;

param = cmd.Parameters.Add("@vcApplicationConstName",
SqlDbType.VarChar);
param.Value = sAppConstName;

if (conn.State == ConnectionState.Closed)
conn.Open();

cmd.ExecuteNonQuery();
conn.Close();

// check for null here
if (cmd.Parameters["@iApplicationID"].Value != null){
iID = (int)cmd.Parameters["@iApplicationID"].Value;
}
else{
throw new ApplicationException("Unable to retrieve Application ID for
application: " + sAppConstName);
}

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]

@vcApplicationConstName varchar(50),
@iApplicationID int OUTPUT

AS

SET @iApplicationID = null

GO

Thanks in advance for your help.

Steven
 
Hello

Compare with DBNull.Value instead of null.

if (cmd.Parameters["@iApplicationID"].Value != DBNull.Value)
{
.....
}


Best regards,
Sherif
 
Steven said:
I'm calling a stored procedure which has an output parameter of type int.
Once the stored procedure is executed, I want to check the value of the
parameter in case it is null. However, when the a null value is returned I
don't seem to be able to detect it.

Any help would be greatly appreciated.

C# code is as follows:

SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add("@iApplicationID",
SqlDbType.Int);
param.Direction = ParameterDirection.Output;

param = cmd.Parameters.Add("@vcApplicationConstName",
SqlDbType.VarChar);
param.Value = sAppConstName;

if (conn.State == ConnectionState.Closed)
conn.Open();

cmd.ExecuteNonQuery();
conn.Close();

// check for null here
if (cmd.Parameters["@iApplicationID"].Value != null){
iID = (int)cmd.Parameters["@iApplicationID"].Value;
}
else{
throw new ApplicationException("Unable to retrieve Application ID for
application: " + sAppConstName);
}

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]

@vcApplicationConstName varchar(50),
@iApplicationID int OUTPUT

AS

SET @iApplicationID = null

GO

Thanks in advance for your help.

Steven
Click to expand...

MSDN says:
When sending a null parameter value to the server, the user must specify
DBNull, not null. The null value in the system is an empty object that
has no value. DBNull is used to represent null values

So try
cmd.Parameters["@iApplicationID"].Value != DBNull.Value

Dmitry
 
"NULLl" is a valid value and it's not the same as "null" - the literal that
represents a null reference.

Here is some info on DBNull class.

http://msdn.microsoft.com/library/d...us/cpref/html/frlrfSystemDBNullClassTopic.asp


Wilford Munley said:
Hello

Compare with DBNull.Value instead of null.

if (cmd.Parameters["@iApplicationID"].Value != DBNull.Value)
{
....
}


Best regards,
Sherif


Steven said:
I'm calling a stored procedure which has an output parameter of type int.
Once the stored procedure is executed, I want to check the value of the
parameter in case it is null. However, when the a null value is returned I
don't seem to be able to detect it.

Any help would be greatly appreciated.

C# code is as follows:

SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add("@iApplicationID",
SqlDbType.Int);
param.Direction = ParameterDirection.Output;

param = cmd.Parameters.Add("@vcApplicationConstName",
SqlDbType.VarChar);
param.Value = sAppConstName;

if (conn.State == ConnectionState.Closed)
conn.Open();

cmd.ExecuteNonQuery();
conn.Close();

// check for null here
if (cmd.Parameters["@iApplicationID"].Value != null){
iID = (int)cmd.Parameters["@iApplicationID"].Value;
}
else{
throw new ApplicationException("Unable to retrieve Application ID for
application: " + sAppConstName);
}

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]

@vcApplicationConstName varchar(50),
@iApplicationID int OUTPUT

AS

SET @iApplicationID = null

GO

Thanks in advance for your help.

Steven
Click to expand...
Click to expand...

Why are DBNull and null different? Aren't they the same thing?

Wilford
Click to expand...
 
Back
Top