Introduction

This is a continuation of my previous article

How Dynamic SQL Can Be Static SQL. You do not need to read my

previous article to understand this one. In this article I will

explain a few more techniques that will allow you to modify the results of a

static SQL query using variables. They are extremely useful if you

don't want to create multiple queries that have only a few minor

differences.

Throughout this article I will use the Northwind database for all sample

code.

Example 1 – Greater Than and Less Than

This example will show how to dynamically determine greater than and less

than using only one condition statement in the where clause. To

accomplish this I'll use the CASE function and a variable called @sign.

We'll suppose we want to see all employees that have a birthday greater than

or less than a date we specify. The following code will do this:

DECLARE @Sign char(1), @date datetime
 
SET @Sign = '>'
SET @date = '7/3/1963'
 
SELECT *
FROM employees
WHERE CASE @Sign WHEN '>' THEN BirthDate
   WHEN '<' THEN @date END >
               CASE @Sign WHEN '>' THEN @date
  WHEN '<' THEN BirthDate END

You'll notice that when you use the greater than sign you get everyone with

a birth date greater than the date assigned to @date and when you change the

sign to less than you get everyone with a birth date less than the date in

@date.

Example 2 – Where Clause Condition Elimination

This example will show how you can use variables to eliminate or use various

conditions in a where clause. In this example we'll look at having

only two conditions in our where clause. Here is the code you can use:

DECLARE @LastName varchar(30), @FirstName varchar(30)
 
SET @LastName = 'd'
SET @FirstName = 'a'
 
SELECT *
FROM employees
WHERE CASE WHEN @LastName <> '' THEN LastName
               WHEN @LastName = '' THEN 'Eliminate' END Like
      CASE WHEN @LastName <> '' THEN @LastName + '%'
               WHEN @LastName = '' THEN 'Eliminate' END
AND
      CASE WHEN @FirstName <> '' THEN FirstName
               WHEN @FirstName = '' THEN 'Eliminate' END Like
      CASE WHEN @FirstName <> '' THEN @FirstName + '%'
               WHEN @FirstName = '' THEN 'Eliminate' END

You'll notice that running it as is in the Northwind data base you should

get only one record. Now delete the a from the @FirstName variable and

rerun the query. You should see another name appear. Now bring

back the a and delete the d from @LastName and rerun the query. One of

the names will change. Now delete the a again and run the query with

both variables equal to ''. You'll get all records.

Example 3 – Cascading Where Clause Conditions

This next example I have called cascading where clause conditions because it

uses only certain variables bases on a specific precedence and each CASE

function takes the first qualifying condition and then moves on. Here

is the code you can run in Query Analyzer:

DECLARE @LastName varchar(30), @FirstName varchar(30), @Country varchar(5), @City varchar(20),
@Title varchar(30)
 
SET @Country = 'usa'
SET @City = 'k'
SET @LastName = ''
SET @FirstName = ''
SET @Title = 's'
 
SELECT *
FROM employees
WHERE CASE WHEN @Title <> '' THEN Title
               WHEN @Country <> '' THEN Country
               WHEN @City <> '' THEN City
               WHEN @LastName <> '' THEN LastName
               WHEN @FirstName <> '' THEN FirstName END LIKE
      CASE WHEN @Title <> '' THEN @Title + '%'
               WHEN @Country <> '' THEN @Country
               WHEN @City <> '' THEN '%' + @City + '%'
               WHEN @LastName <> '' THEN @LastName + '%'
               WHEN @FirstName <> '' THEN @FirstName + '%' END
AND
      CASE WHEN @Title <> '' THEN CASE WHEN @Country = '' THEN 'Eliminate' ELSE Country END
               WHEN @Country <> '' THEN CASE WHEN @City = '' THEN 'Eliminate' ELSE City END
               WHEN @City <> '' THEN CASE WHEN @LastName = '' THEN 'Eliminate' ELSE LastName END
               WHEN @LastName <> '' THEN CASE WHEN @FirstName = '' THEN 'Eliminate' ELSE FirstName END
               WHEN @LastName = '' THEN 'Eliminate'
               WHEN @FirstName = '' THEN 'Eliminate' END LIKE
      CASE WHEN @Title <> '' THEN CASE WHEN @Country = '' THEN 'Eliminate' ELSE @Country END
               WHEN @Country <> '' THEN CASE WHEN @City = '' THEN 'Eliminate' ELSE @City + '%' END
               WHEN @City <> '' THEN CASE WHEN @LastName = '' THEN 'Eliminate' ELSE '%' + @LastName + '%' END
               WHEN @LastName <> '' THEN CASE WHEN @FirstName = '' THEN 'Eliminate' ELSE '%' + @FirstName + '%' END
               WHEN @LastName = '' THEN 'Eliminate'
               WHEN @FirstName = '' THEN 'Eliminate' END
AND
      CASE WHEN @Title <> '' THEN City
               WHEN @Country <> '' THEN LastName
               WHEN @City <> '' THEN FirstName
               WHEN @LastName = '' THEN 'Eliminate'
               WHEN @LastName <> '' THEN 'Eliminate'
               WHEN @FirstName <> '' THEN 'Eliminate' END LIKE
      CASE WHEN @Title <> '' THEN '%' + @City + '%'
               WHEN @Country <> '' THEN @LastName + '%'
               WHEN @City <> '' THEN '%' + @FirstName + '%'
               WHEN @LastName = '' THEN 'Eliminate'
               WHEN @LastName <> '' THEN 'Eliminate'
               WHEN @FirstName <> '' THEN 'Eliminate' END

The first precedence is on the title. When there is a value for @Title

the only variables used will be @Title, @Country, and @City. If you

use SET @Title = '' you'll find that the only variables used are @Country,

@City, and @LastName. When you SET @Country = '' the only variables

used will be @City, @LastName, and @FirstName. Having @Title = '',

@Country = '', and @City = '' will make the WHERE clause only use @LastName

and @FirstName. Finally, having all the variables set equal to ''

except @FirstName will make the WHERE clause only use @FirstName. Here

are some sample SET combinations you can use with this query:

SET @Country = 'uk' SET @City = 'l' SET @LastName = 'b' SET @FirstName = ''
SET @Title = ''
 
SET @Country = '' SET @City = 'l' SET @LastName = 'a' SET @FirstName = 'a'
SET @Title = ''
 
SET @Country = '' SET @City = '' SET @LastName = 'd' SET @FirstName = 'n'
SET @Title = ''
 
SET @Country = '' SET @City = '' SET @LastName = '' SET @FirstName = 'a'
SET @Title = ''

This query was intentionally designed to not return all rows if all

variables are set equal to ''. This shows you an example of only

allowing certain combinations of result sets controlled by what variables

have values and if more than three values are provided then there is a

systematic way to select which three should be used.

Conclusions

This article shows a few ways to design static SQL that can have different

records returned depending on what values are sent into variables.

Using these techniques you can develop compact code and have only one query

do all the work. This is an alternative to using dynamic SQL.