testing for a null value from a stored proc

[Andrew Baker]

I have the following code that calles a stored proc in SQLServer. When the
output parameter @custref is null (System.DBNull) I cant seem to find a
test for this and I get an exception. I know I could coalesce the stored
proc, but I would like to know if the value is null.

How do you test for null in the returned parameter of a stored proc?

TIA
Andrew.
Dim retstr As String = ""

' Create Instance of Connection and Command Object

Dim myConnection As SqlConnection = New SqlConnection(Me.sSqlString)

Dim myCommand As SqlCommand = New SqlCommand("bo_getCustRef", myConnection)

' Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC

Dim parameterCode As SqlParameter = New SqlParameter("@CustCode",
SqlDbType.NVarChar, 8)

parameterCode.Value = code

myCommand.Parameters.Add(parameterCode)

Dim parameterRef As SqlParameter = New SqlParameter("@custRef",
SqlDbType.NVarChar, 20)

parameterRef.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterRef)

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

Catch XcpInvOp As System.Exception

Finally

myConnection.Close()

myConnection.Dispose()

myConnection = Nothing

If Not parameterRef.Value Is Nothing Then

retstr = CStr(parameterRef.Value)

End If

End Try

Return retstr

[Imran Koradia]

"Andrew Baker" <[e-mail]> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I have the following code that calles a stored proc in SQLServer. When the output parameter @custref is null (System.DBNull) I cant seem to find a
test for this and I get an exception. I know I could coalesce the stored
proc, but I would like to know if the value is null.

How do you test for null in the returned parameter of a stored proc?

TIA
Andrew.
Dim retstr As String = ""

' Create Instance of Connection and Command Object

Dim myConnection As SqlConnection = New SqlConnection(Me.sSqlString)

Dim myCommand As SqlCommand = New SqlCommand("bo_getCustRef", myConnection)
' Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC

Dim parameterCode As SqlParameter = New SqlParameter("@CustCode",
SqlDbType.NVarChar, 8)

parameterCode.Value = code

myCommand.Parameters.Add(parameterCode)

Dim parameterRef As SqlParameter = New SqlParameter("@custRef",
SqlDbType.NVarChar, 20)

parameterRef.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterRef)

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

Catch XcpInvOp As System.Exception

Finally

myConnection.Close()

myConnection.Dispose()

myConnection = Nothing

If Not parameterRef.Value Is Nothing Then
Can't you check the Value for System.DBNull here..that is something like..
If Not parameterRef.Value = System.DBNull Then
???
retstr = CStr(parameterRef.Value)

End If

End Try

Return retstr

[Greg Burns]

"I know I could coalesce the stored proc, but I would like to know if the
value is null."

I used to use COALESCE until I learned about ISNULL. It is more efficient.

To check for null in a parameter use:

If Not parameterRef.Value Is System.DBNull.Value Then

HTH,
Greg

"Imran Koradia" <no****@microsoft.com> wrote in message
news:uN*************@TK2MSFTNGP12.phx.gbl...

"Andrew Baker" <[e-mail]> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I have the following code that calles a stored proc in SQLServer. When

the

output parameter @custref is null (System.DBNull) I cant seem to find a
test for this and I get an exception. I know I could coalesce the stored proc, but I would like to know if the value is null.

How do you test for null in the returned parameter of a stored proc?

TIA
Andrew.
Dim retstr As String = ""

' Create Instance of Connection and Command Object

Dim myConnection As SqlConnection = New SqlConnection(Me.sSqlString)

Dim myCommand As SqlCommand = New SqlCommand("bo_getCustRef",

myConnection)

' Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC

Dim parameterCode As SqlParameter = New SqlParameter("@CustCode",
SqlDbType.NVarChar, 8)

parameterCode.Value = code

myCommand.Parameters.Add(parameterCode)

Dim parameterRef As SqlParameter = New SqlParameter("@custRef",
SqlDbType.NVarChar, 20)

parameterRef.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterRef)

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

Catch XcpInvOp As System.Exception

Finally

myConnection.Close()

myConnection.Dispose()

myConnection = Nothing

If Not parameterRef.Value Is Nothing Then

Can't you check the Value for System.DBNull here..that is something like..
If Not parameterRef.Value = System.DBNull Then
???

retstr = CStr(parameterRef.Value)

End If

End Try

Return retstr

[Andrew Baker]

Greg your a champion.

I had tried If Not parameterRef.Value = System.DBNull.Value Then
which was a compile error.

One click and 3 characters later and it works :)

Andrew
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

"I know I could coalesce the stored proc, but I would like to know if the
value is null."

I used to use COALESCE until I learned about ISNULL. It is more efficient.

To check for null in a parameter use:

If Not parameterRef.Value Is System.DBNull.Value Then

HTH,
Greg

"Imran Koradia" <no****@microsoft.com> wrote in message
news:uN*************@TK2MSFTNGP12.phx.gbl...

"Andrew Baker" <[e-mail]> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I have the following code that calles a stored proc in SQLServer. When

the

output parameter @custref is null (System.DBNull) I cant seem to find

a test for this and I get an exception. I know I could coalesce the stored proc, but I would like to know if the value is null.

How do you test for null in the returned parameter of a stored proc?

TIA
Andrew.
Dim retstr As String = ""

' Create Instance of Connection and Command Object

Dim myConnection As SqlConnection = New SqlConnection(Me.sSqlString)

Dim myCommand As SqlCommand = New SqlCommand("bo_getCustRef",

myConnection)

' Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC

Dim parameterCode As SqlParameter = New SqlParameter("@CustCode",
SqlDbType.NVarChar, 8)

parameterCode.Value = code

myCommand.Parameters.Add(parameterCode)

Dim parameterRef As SqlParameter = New SqlParameter("@custRef",
SqlDbType.NVarChar, 20)

parameterRef.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterRef)

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

Catch XcpInvOp As System.Exception

Finally

myConnection.Close()

myConnection.Dispose()

myConnection = Nothing

If Not parameterRef.Value Is Nothing Then

Can't you check the Value for System.DBNull here..that is something

like.. If Not parameterRef.Value = System.DBNull Then
???

retstr = CStr(parameterRef.Value)

End If

End Try

Return retstr

[Jeff Johnson [MVP: VB]]

"Andrew Baker" <[e-mail]> wrote in message
news:OW*************@TK2MSFTNGP11.phx.gbl...

I had tried If Not parameterRef.Value = System.DBNull.Value Then
which was a compile error.

Null never "equals" anything. All expressions involving Null return Null
(except concatenating an empty string, which returns the empty string) and
Null is treated as Boolean False.