Ms SQL Server Consultant

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.

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

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.

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.