Blog
SQL 2012: Developer: NULLs and SqlBoolean
By Frank Tillinghast | March 06, 2014
When integrating T-SQL with the CLR, remember to declare variables, parameters, and return values of data types exposed through the System.Data.SqlTypes namespace. Doing so guarantees a behavior more similar to T-SQL. As described in the previous section, the outcome of performing arithmetic, bitwise, and logical comparisons between two variables when one or both values is NULL can be inconsistent. The ANSI_NULLS option in T-SQL proves how different the results can be; and as you saw in the simple Visual Basic .NET example, not using the SqlTypes data types leads to the same confusion.
Fortunately, there is the SqlBoolean data type. Exposed as part of the SqlTypes namespace, the SqlBoolean data type can represent three distinct states-true, false, and unknown. In addition, the comparison of two SqlTypes data types always returns a SqlBoolean, which again ensures consistent behavior. The SqlBoolean data type exposes three important properties:
- IsTrue: Indicates whether the comparison produces a TRUE value.
- IsFalse: The outcome when the comparison is FALSE.
- IsNull: Returns true when the comparison between the variables produces an unknown or NULL result.
Keeping these concepts in mind, look at the Visual Basic .NET code behind the SqlBooleans button on the switchboard form.
Private Sub SqlBooleans() Dim intX As SqlInt32 Dim intY As SqlInt32 Dim blnResult As SqlBoolean intX = 5 intY = SqlInt32.Null blnResult = (intX = intY) MsgBox("intX IS NULL - " & intX.IsNull & vbTab _ & "intY IS NULL - " & intY.IsNull & vbTab _ & "intX = intY - " _ & blnResult.IsTrue.ToString) End Sub The code makes the following comparison:
blnResult = (intX = intY) The code compares intX-a SqlInt32 assigned the value 5-with intY, another SqlInt32 explicitly assigned a NULL value. The result is a SqlBoolean data type with properties that contain the outcome of the comparison- blnResult.IsTrue. Figure 1 shows the Msgbox that displays the outcome of this routine. 
Figure 1. SqlBooleans provide consistency when you work with NULL values.
WARNING! Remember that a SqlBoolean data type represents three states- IsTrue, IsFalse, and IsNull. IsNull returns TRUE only when both sides of the comparison are unknown.
