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_ GetApplicationI D", conn);
cmd.CommandType = CommandType.Sto redProcedure;

SqlParameter param = cmd.Parameters. Add("@iApplicat ionID",
SqlDbType.Int);
param.Direction = ParameterDirect ion.Output;

param = cmd.Parameters. Add("@vcApplica tionConstName",
SqlDbType.VarCh ar);
param.Value = sAppConstName;

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

cmd.ExecuteNonQ uery();
conn.Close();

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

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetAppli cationID]

@vcApplicationC onstName 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["@iApplicationI D"].Value != DBNull.Value)
{
.....
}
Best regards,
Sherif
"Steven" <st************ @virgin.net> wrote in message
news:bo******** ***********@new s.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_ GetApplicationI D", conn);
cmd.CommandType = CommandType.Sto redProcedure;

SqlParameter param = cmd.Parameters. Add("@iApplicat ionID",
SqlDbType.Int);
param.Direction = ParameterDirect ion.Output;

param = cmd.Parameters. Add("@vcApplica tionConstName",
SqlDbType.VarCh ar);
param.Value = sAppConstName;

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

cmd.ExecuteNonQ uery();
conn.Close();

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

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetAppli cationID]

@vcApplicationC onstName 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_ GetApplicationI D", conn);
cmd.CommandType = CommandType.Sto redProcedure;

SqlParameter param = cmd.Parameters. Add("@iApplicat ionID",
SqlDbType.Int);
param.Direction = ParameterDirect ion.Output;

param = cmd.Parameters. Add("@vcApplica tionConstName",
SqlDbType.VarCh ar);
param.Value = sAppConstName;

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

cmd.ExecuteNonQ uery();
conn.Close();

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

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetAppli cationID]

@vcApplicationC onstName 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["@iApplicationI D"].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****@tinkert oys.net> wrote in message
news:s5******** ************@ad elphia.com...

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

news:u9******** ******@TK2MSFTN GP09.phx.gbl...

Hello

Compare with DBNull.Value instead of null.

if (cmd.Parameters["@iApplicationI D"].Value != DBNull.Value)
{
....
}
Best regards,
Sherif
"Steven" <st************ @virgin.net> wrote in message
news:bo******** ***********@new s.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_ GetApplicationI D", conn);
cmd.CommandType = CommandType.Sto redProcedure;

SqlParameter param = cmd.Parameters. Add("@iApplicat ionID",
SqlDbType.Int);
param.Direction = ParameterDirect ion.Output;

param = cmd.Parameters. Add("@vcApplica tionConstName",
SqlDbType.VarCh ar);
param.Value = sAppConstName;

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

cmd.ExecuteNonQ uery();
conn.Close();

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

Stored Procedure (for test purposes):

CREATE PROCEDURE [dbo].[sp_BSQ_GetAppli cationID]

@vcApplicationC onstName 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