Skip to content

Databases

Dynamic Search Without Using Dynamic SQL

There can be some scenarios where you have to search a database using multiple criteria where you don’t know in advance as which criterion or the combination of criteria the end user would select.

For example, consider the following scenario

There’s a table which contains some basic information about employees, say it has the following columns:

      • FirstName
      • LastName
      • Gender
      • EmailId
      • ContactNo
      • HireDate

Now your front-end user interface has a Form that is used for searching and has above input fields. The Form is flexible enough to allow user to input any one, or a combination of any of the fields or none of the fields.

What are the possible solutions?

Before SQL Server 2008 the developers used to write Dynamic SQL to sort this out. Dynamic SQL has some drawbacks like degraded performance (as Dynamic SQL will not reuse query execution plan) and troublesome maintenance because Dynamic SQLs are error prone and a bit complex in nature.

We’ll sort out this problem using Static SQL by using OR, AND logical operator in WHERE clause.

Step# 1

NOTE: This example has been created using SQL Server 2012.

Create the table along with data. You can download the following script and execute it in SSMS to create the table along with the sample data required for this example.


Step# 2

Create the Stored Procedure that can be used to fetch results based on various input parameters. You can download and execute the following script.

Note: You can gain some performance if you add the query hint OPTION (RECOMPILE) at the end of the stored procedure.


See the following screen grab to get a feel of the result:

Results
Hope this helps a few of the developers. Feel free to share your views if you feel this can be improved.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)