Stored procedure parameter output value

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
 
S

Sherif ElMetainy

Hello

Compare with DBNull.Value instead of null.

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


Best regards,
Sherif
 
D

Dmitry Baibakov

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

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
 
O

oj

"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

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

Wilford
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top