Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How Dynamic SQL Can Be Static SQL Part 2

By Robert Marda,

How Dynamic SQL Can Be Static SQL Part 2

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.

 

Total article views: 10771 | Views in the last 30 days: 5
 
Related Articles
FORUM

how to devied fullname into firstname and lastname

how to devied fullname into firstname and lastname

FORUM

eliminating duplicates

eliminating duplicates

FORUM

Lastname, Firstname switch

I dont know if this is any help but this is the code I started with: SELECT HOST9006.DESCRIPTION,...

FORUM

Split out FirstName, LastName, MiddleName

One column name as [MemberName] in which stored data like LastName, FirstName M (with initial mi...

FORUM

Create FirstName and LastName to Replace Existing FirstName and LastName

Hello Everyone I hope that you all are having a very nice day. I am wanting to change all the Fi...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones