Blog

SQL 2012: Developer: NULLs and SqlBoolean

By Frank Tillinghast

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. Sqlbool1 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.

 



Frank Tillinghast
Frank Tillinghast is a senior consultant with MTOW Software Solutions. He is a Microsoft Certified Solution Developer and has been developing applications for over 15 years. Most of his time is spent consulting for companies nationwide with troubled projects or mentoring projects to successful completion. When he is not developing software or mentoring clients, Frank is teaching other developers. He has taught thousands of application developers how to create business solutions with Visual Studio .NET. VB.NET, ASP.NET, Visual C#, and SQL Server.


This blog entry was originally posted March 06, 2014 by Frank Tillinghast

SQL Server SQL Developer