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

[Jeff Louie]

Me thinks you mispoke. Here is the what is said:

In database applications, a null object is a valid value for a _field_.
This class
differentiates between a null value (a null object) and an uninitialized
value
(the DBNull.Value instance).

In other words:

NULL is not a value at all. NULL is a placeholder for missing
information.

Regards,
Jeff
Author. Visual Cafe for Java Explorer, Database Development Edition
Coriolis
1998.

"NULLl" is a valid value and it's not the same as "null" - the literal

that
represents a null reference.<

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!