CREATE PROCEDURE ReadPerson
@FirstName varchar,
@LastName varchar,
@PhoneNumber varchar,
@ZipCode varchar,
@Age int,
@IsMarried bit
AS
BEGIN
SELECT *
FROM Person p
WHERE p.FirstName = @FirstName
AND p.LastName = @LastName
AND p.PhoneNumber = @PhoneNumber
AND p.ZipCode = @ZipCode
AND p.Age = @Age
AND p.IsMarried = @IsMarried
END
GO
Which could be executed like this:Exec ReadPerson 'John', 'Doe', '8005551234', '90210', 50, 1But now, let's say the user doesn't know the phone number. We now have a dilema. Should we write another stored procedure that eliminates the phone number from the WHERE clause. Not a good idea, since we will probably have to write more stored procedures that provide ALL of the combinations of the filter. We could re-write this stored procedure to interrogate each parameter and "build" a SELECT statement with Dynamic SQL. Not a good idea either, since Dynamic SQL is (1) hard to read, and (2) cannot validate or intellisense table names or columns within the single quotes (''). There are other solutions that are equally bad, but there is one that works just great!
Enter Short-Circuit evaluation! T-SQL (SQL 2008 and above) now perfoms short-circuit evaluations of expressions. But what is "Short-Circuiting"? A brief explanation.
Most high-level programming languages are able to evaluate boolean expressions using an optimization called short-circuiting, which can stop evaluating an expression as soon as the result can be determined.
For example, an "AND" operator can stop evaluating a combined expression as soon as it finds the first expression that evaluates to false.
IF (1 = 1 AND 1 = 5 AND 5 = 5)In this evaluation, the order of the combined expression is key. The result of the combined expression will obviously be false. However, that last expression { 5 = 5 } is NEVER evaluated! The reason is that an AND operator requires all expressions to evaluate to true. Since the second expression { 1 = 5 } evaluates to false, the combined expression can immediately evalutate to false.
The same short-circuit evaluation occurs with an "OR" operator, that stops evaluating a combined expression as soon as it finds the first expression that evaluates to true.
IF (1 = 1 OR 1 = 5 OR 5 = 5)Again, the order of the combined expression is key. The result of the combined expression will obviously be true. However, that second and third expressions { 1 = 5 OR 5 = 5 } are NEVER evaluated! The reason is that an OR operator requires only one expressions to evaluate to true. Since the first expression { 1 = 1 } evaluates to true, the combined expression can immediately evalutate to true.
Since Short-Circuit evaluation is now available to us, we can tackle our challenge with a VERY optimized stored procedure. Let's take a look at a re-write of the above stored procedure that uses Short-Circuit evaluation.
CREATE PROCEDURE ReadPerson
@FirstName varchar,
@LastName varchar,
@PhoneNumber varchar,
@ZipCode varchar,
@Age int,
@IsMarried bit
AS
BEGIN
SELECT *
FROM Person p
WHERE ((@FirstName = NULL) OR (p.FirstName = @FirstName))
AND ((@LastName = NULL) OR (p.LastName = @LastName))
AND ((@PhoneNumber = NULL) OR (p.PhoneNumber = @PhoneNumber))
AND ((@ZipCode = NULL) OR (p.ZipCode = @ZipCode))
AND ((@Age = NULL) OR (p.Age = @Age))
AND ((@IsMarried = NULL) OR (p.IsMarried = @IsMarried))
END
GO
Which could be executed the same as before:Exec ReadPerson 'John', 'Doe', '8005551234', '90210', 50, 1But now adds all different kinds of combinations
-- Returns any record with FirstName is John
Exec ReadPerson 'John', NULL, NULL, NULL, NULL, NULL
-- Returns any record with FirstName is John and age 50
Exec ReadPerson 'John', NULL, NULL, NULL, 50, NULL
-- Returns any record with LastName is Doe and ZipCode is 90210
Exec ReadPerson NULL, 'Doe', NULL, '90201', NULL, NULL
Let's look at why. Check out the first condition in the WHERE clauseWHERE ((@FirstName = NULL) OR (p.FirstName = @FirstName))Because it is an OR condition, if the first expression (@FirstName = NULL), then the rest of the combined expression (p.FirstName = @FirstName) won't be evaluated. If FirstName is NULL, the results are NOT Filtered by FirstName. However, if the first expression is false (such as when @FirstName is 'John'), then the rest of the combined expression will still be evaulated. Then, the results WILL BE Filtered by all records where FirstName field is 'John'. Since this combined expression is all encapsulated in it's own parenthesis, it is evaluated completely separate from all of the Other conbined expressions that are separated by AND.
Cool, huh???
