# Calling stored procedure with a sqlCommand

I have the following code as part of an sqlCommand which is calling a stored procedure:

Dim groupObj

If groupId = 0 Then
groupObj = DBNull.Value
Else
groupObj = groupId
End If

Dim siteObj

If siteId = 0 Then
siteObj = DBNull.Value
Else
siteObj = siteId
End If



I basically want to check for a condition and (if true) send a DBNull instead. This code looks awful however - is there a way to succinct this up?

• You should probably use nullable types for the inputs. Nov 9, 2011 at 23:04
• You might want to check out Nullable Types and ?? Operator. You can significantly decrease the no of lines and improve your code. Jan 2, 2012 at 8:24
• I definitely agree with using nullable types. Just to clarify that further, the SqlParameter class should have no problem with taking a plain old .NET null / Nothing value and automatically translating that into a database null value. DBNull generally comes into play when you are reading data out of a database, rather than writing to it. Jan 3, 2012 at 20:00
• I like this answer. Would upvote if it provided just a little more specifics. Additionally, the ?? operator would not be applicable in this specific situation. Finally, ?? is the C# coalesce operator; the VB.NET equivalent would be to call the If function with only 2 arguments. Jan 3, 2012 at 20:13
• Actually, .NET will not translate null/nothing into a DBNull.Value. A SqlParameter with a null value will not be sent to the database at all. You can easily test this by creating a stored proc (or possibly prepared statement) with a parameter (with no default value) and try to send it in as a .NET null. You should get an exception saying stored procedure expected parameter but it was not passed in. if you have nulls that you want to send in you must explicitly set them to DBNull.Value. Mar 29, 2012 at 12:36

You can use the ternary operator If():

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = If(groupID = 0, DBNull.Value, groupID)
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = If(siteId = 0, DBNull.Value, siteId)


Be aware that this can cause a compiler error if you have Option Strict turned on. The following is Option Strict compatible...

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = If(groupID = 0, DBNull.Value, CObj(groupID))
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = If(siteId = 0, DBNull.Value, CObj(groupID))


The If ternary operator was added in .Net 3.5/Visual Basic 2008. If you're using and older version you'll need to use the IIF function. The syntax is almost the same, just replace if with iif

sqlCommand.Parameters.Add(New SqlClient.SqlParameter("GroupID", SqlDbType.Decimal)).Value = IIF(groupID = 0, DBNull.Value, groupID)
sqlCommand.Parameters.Add(New SqlClient.SqlParameter("SiteID", SqlDbType.Decimal)).Value = IIF(siteId = 0, DBNull.Value, siteId)


You could define an extension method like this (I'm not a VB programmer so please excuse the syntax errors):

<Extension()>
Public Sub OrDBNull(ByVal val As Decimal)
If val = 0
return DBNull.Value
End If

return val ;
End Sub


So your command parameters would become:

sqlCommand.Parameters.Add(New SqlParameter("GroupID", SqlDbType.Decimal)).Value = groupObj.OrDBBull()

<Extension()>