Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

SQL Expressions


Boolean Constants


Introduction



Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false (there is no middle result; in other words, something is not half true or half false or "Don't Know": either it is true or it is false).

The TRUE and FALSE Constants



In Boolean algebra, something is considered TRUE when it holds a value. The value is also considered as 1 or Yes. By contrast, if something doesn't hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the SPHome (which specifies whether a student lives in a single parent home) field of the Students table of the HighSchool database. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

The NULL Constant



After you have declared a variable, the SQL interpreter reserves a space in the computer memory for it but doesn't put anything in that memory space. At that time, that area of memory doesn't hold a significant value. Also at that time, the variable is considered null.

Here is note to be careful about: when a variable is said to hold a null value, it doesn't mean its value is 0. It doesn't even mean that the variable's memory space is empty. It actually means that we cannot clearly determine the current value that the variable is holding.

To support the null value, Transact-SQL provides a constant named NULL. The NULL constant is mostly used for comparison purposes. For example, you can use an IF statement to check the nullity of a variable.

The IS Operator



To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. Here is an example:

-- Square Calculation
DECLARE @Side As Decimal(10,3),
@Perimeter As Decimal(10,3),
@Area As Decimal(10,3);

SET @Perimeter = @Side * 4;
SET @Area = @Side * @Side;
IF @Side IS NULL
PRINT 'A null value is not welcome'
ELSE IF @Side > 0
BEGIN
SELECT @Side AS Side;
SELECT @Perimeter AS Perimeter ;
SELECT @Area AS Area;
END;
ELSE
PRINT 'You must provide a positive value';
GO


To avoid having a NULL value, you can either initialize the variable or you can assign it a value. Here is an example:

-- Square Calculation
DECLARE @Side As Decimal(10,3),
@Perimeter As Decimal(10,3),
@Area As Decimal(10,3);
SET @Side = 48.126;
SET @Perimeter = @Side * 4;
SET @Area = @Side * @Side;
IF @Side IS NULL
PRINT 'A null value is not welcome'
ELSE IF @Side > 0
BEGIN
SELECT @Side AS Side;
SELECT @Perimeter AS Perimeter ;
SELECT @Area AS Area;
END;
ELSE
PRINT 'You must provide a positive value';
GO




source : http://blogs.msdn.com/sharepoint/default.aspx