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:
Hope this helps a few of the developers. Feel free to share your views if you feel this can be improved.
Basant Singh
Latest posts by Basant Singh (see all)
- Go Maps Introduction for Beginners - May 20, 2015
- Go Programming – Arrays and Slices - May 11, 2015
- Golang Switch Case with Sample Code - May 1, 2015
