Introduction

I have seen various developers in my company writing dynamic queries using variables in the stored procedures. They have been using variables to store larger and complex queries in stored procedures. Recently I read the article How to Build Dynamic Stored Procedures posted by Robert Marda which describes one way to do this.

I have observed that most of the time only WHERE clause needs to be dynamic in the procedures. The SELECT list and FROM clause remains static. People tend to choose variables so that they can build where clause dynamically using IF conditions and putting a query in a variable gives trouble at time of debugging and maintenance of the query. I was following this same path until I had to invest considerably big amount of time to identify and fix a syntactical error in my query. Hence I revisited my query and found that I only needed build where clause dynamically. For a three liner WHERE clause I had put whole query in a variable.

After looking at various approaches I came up with a different solution to write such queries. Here I will explain how we can eliminate use of variable while writing queries which product the same result as a dynamically written query will do.

Decision

We can use variables to store the SQL query if,

• the columns in SELECT lists will be generated dynamically
• source table names will be decided at runtime

If the SELECT list is going to be static and we need to take care of only WHERE clause, then we can eliminate use of variable. This will benefit as:-

• Less complexity
• Easy syntax checking (mostly quote (') gives problem to novice developers when query is stored in a variable)
• Easy maintenance

Process

To understand the different approach we will require some basic data as explained below.
Let's create a simple table EmployeeDetails.

CREATE TABLE EmployeeDetails
(
 Employee_Name VARCHAR(50),
 Gender CHAR(1),
 Age INT
)
GO

We have done with an empty table creation. Now fill the table with data.

INSERT INTO EmployeeDetails VALUES ('Sunil','M',30)
GO
INSERT INTO EmployeeDetails VALUES ('Jimmy','F',24)
GO
INSERT INTO EmployeeDetails VALUES ('David','M',25)
GO
INSERT INTO EmployeeDetails VALUES ('Ravina','F',21)
GO

We are ready with the data. Let's go ahead and write stored procedure to fetch the details from EmployeeDetails depending on various input parameters.

Let's create a SP named uspGetEmpDetails which will have two input parameters,
- Gender
- Age

We will keep both parameters defaulted to NULL. This way we can match query results as per the input values provided.

CREATE PROCEDURE uspGetEmpDetails
 @Gender CHAR(1) = NULL,
 @Age INT = NULL
AS
BEGIN
 SELECT Employee_Name, Gender, Age FROM EmployeeDetails 
 WHERE...
END

Here comes the trick. During execution out SP can have a input value for a single parameter or for the both the parameters.
I can search all employees,
- having Gender as 'M' OR
- whose age is above 25 OR
- having Gender as 'M' and whose age is above 25

Editor's Note: There is a lot of debate about this structure among people. Please read the discussion for the article, to be sure you understand how this works.

Looking at the input conditions, we can figure out that input value of a parameter either can be NULL or it can be something valid value.
So we can write the WHERE condition as,

...WHERE ( @Gender IS NULL OR Gender = @Gender) AND (@Age IS NULL OR Age > @Age)

Comparing the parameter values in this way we are sure about, If value for @Gender is NULL, second part of OR condition will be eliminated. This gives impression as good as Gender is not being compared in WHERE condition and only AGE is considered. Vice-versa for the parameter AGE.

Complete look at SP will be like,

CREATE PROCEDURE uspGetEmpDetails
           @Gender CHAR(1) = NULL,
           @Age INT = NULL
AS
BEGIN
           SELECT Employee_Name, Gender, Age FROM EmployeeDetails 
           WHERE ( @Gender IS NULL OR Gender = @Gender) 
           AND ( @Age IS NULL OR Age > @Age)
END

Now lets execute the procedure by passing appropriate values to it.

1) Fetch all male employees

EXEC uspGetEmpDetails 'M'

I get these results:

 Employee_Name    Gender           Age
 -------------------------------------------------------------
 Sunil            M                30
 David            M                25

2) Fetch all employees having age more than 22 yrs

EXEC uspGetEmpDetails NULL,22

to get these results

Employee_Name       Gender             Age
------------------------------------------------------
Sunil               M                  30
Jimmy               F                  24
David               M                  25

3) Fetch all female employees having age more than 21 yrs

EXEC uspGetEmpDetails 'F',21

which gives

Employee_Name                     Gender            Age
----------------------------------------------------------
Jimmy                             F                 24

Conclusion

In this article I have shown a way to writing queries where conditional part of the query can be written to tally input parameters with actual data at runtime. The query produces the same result as written in a way of using variables and generating WHERE clause using IF conditions. Thank you for giving your time to read this article. The above example it primarily intended to show comparing input parameter values in WHERE clause and generating desired results with ease of writing and debugging the query. Any suggestions and improvements to this are welcome.