Stored procedure parameter output value

[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

[Sherif ElMetainy]

Hello

Compare with DBNull.Value instead of null.

if (cmd.Parameters["@iApplicationID"].Value != DBNull.Value)
{
.....
}
Best regards,
Sherif
"Steven" <st************@virgin.net> wrote in message
news:bo*******************@news.demon.co.uk...

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

[Dmitry Baibakov]

Steven wrote:

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

[Steven]

Excellent. Thanks.

[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/de...ClassTopic.asp
"Wilford Munley" <tr****@tinkertoys.net> wrote in message
news:s5********************@adelphia.com...

"Sherif ElMetainy" <el*************@wayout.net.NOSPAM> wrote in message

news:u9**************@TK2MSFTNGP09.phx.gbl...

Hello

Compare with DBNull.Value instead of null.

if (cmd.Parameters["@iApplicationID"].Value != DBNull.Value)
{
....
}
Best regards,
Sherif
"Steven" <st************@virgin.net> wrote in message
news:bo*******************@news.demon.co.uk...

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