testing for a null value from a stored proc

  • Thread starter Thread starter Andrew Baker
  • Start date Start date
A

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
 
Andrew Baker said:
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
Click to expand...
Can't you check the Value for System.DBNull here..that is something like..
If Not parameterRef.Value = System.DBNull Then
???
 
"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
 
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
 
I had tried If Not parameterRef.Value = System.DBNull.Value Then
which was a compile error.
Click to expand...

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.
 
Back
Top