How Dynamic SQL Can Be
Static SQL Part 2
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.
