Solved

Search Query Dynamic Vs Static - SQL Server 2008 / 2012

Posted on 2014-03-27
2
686 Views
Last Modified: 2014-04-02
I need to write a Search Query (Stored Procedure) wherein the number of input parameters will be more than 15.  User can pass single parameter value or combinations of parameters.

What would be the best approach

 1. Static Approach as below:

     
  DECLARE @FirstName VARCHAR(50) = 'XYZ' ,
        @LastName VARCHAR(50) = 'ABC' ,
        @MiddleName VARCHAR(50) = '999-9999%'';  Select 1 as Abc where 1 like ''%1'
        SELECT *
        FROM   [Person].[Person]
        WHERE  FirstName LIKE '%' + @FirstName + '%'
            OR LastName LIKE '%' + @LastName + '%'
            OR MiddleName LIKE '%' + @MiddleName + '%'

Open in new window

2. Dynamic SQL Approach as below:
       
       
DECLARE @FirstName VARCHAR(50) = 'XYZ', @LastName VARCHAR(50) = 'ABC', @MiddleName VARCHAR(50) = '999-9999%'
    
    	DECLARE @select VARCHAR(5000) = 'Select * from [Person].[Person] '
    	DECLARE @WhereClause VARCHAR(5000) = 'Where'
    	IF (@FirstName IS NOT NULL OR @FirstName <> '')
    		SET @WhereClause = @WhereClause + ' FirstName Like ''%' + @FirstName + '%'''
    
    	IF (@LastName IS NOT NULL OR @LastName<> '') 
    		IF (@WhereClause <> 'Where')
    			SET @WhereClause = @WhereClause + ' OR LastName Like ''%' + @LastName+ '%'''
    		ELSE
    			SET @WhereClause = @WhereClause + ' LastName Like ''%' + @LastName+ '%'''
    
    	IF (@MiddleName IS NOT NULL OR @MiddleName <> '') 
    		IF (@WhereClause <> 'Where')
    			SET @WhereClause = @WhereClause + ' OR MiddleName Like ''%' + @MiddleName + '%'''
    		ELSE
    			SET @WhereClause = @WhereClause + ' MiddleNameLike ''%' + @MiddleName + '%'''
     exec (@select + @WhereClause)

Open in new window

Now the Issues:

With approach 1 I have to include every single parameter within the WHERE clause.  Which I think would be bit of performance degrade and everytime even though the value for a parameter is not supplied it will still search in relevant column.

With approach 2 I think it is likely to SQL Injection.

Example:  if the parameter values in case 2 are passed as

   
 DECLARE @FirstName VARCHAR(50) = 'XYZ', @LastName VARCHAR(50) = 'ABC', @MiddleName VARCHAR(50) = '999-9999%'';  Select 1 as Abc where 1 like ''%1';

Open in new window


the resulting SQL Query which will execute will be

   
Select * from [Person].[Person] Where FirstName Like '%XYZ%' OR LastName Like '%ABC%' OR MiddleName Like '%999-9999%';  Select 1 as Abc where 1 like '%1%'

Open in new window


which is not good.

So what is the best way to code this with best performance.
0
Comment
Question by:Sreedhar Vengala
2 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 39960861
Using variables is surely more safe.  

Such dynamic sql's are dangerous for sql injection, especialy when you can have uncontroled/uncontrolable input strings as part of names
http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

However if you are searching with a variaty of conditions you won't get a good execution plan. So if depending on the parameters you have to search in a very large table to or not building a query without that can be an option.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39961147
you can do like this, simplifying the code, and avoiding sql injection:
DECLARE @FirstName VARCHAR(50) = 'XYZ', @LastName VARCHAR(50) = 'ABC', @MiddleName VARCHAR(50) = '999-9999%'
    
    	DECLARE @select VARCHAR(5000) = 'Select * from [Person].[Person] where  1=0 '

    	IF (@FirstName IS NOT NULL OR @FirstName <> '')
    		SET @select = @select + ' OR FirstName Like ''%'' + @FirstName + ''%'' '
    
    	IF (@LastName IS NOT NULL OR @LastName<> '') 
    		SET @select = @select + ' OR LastName Like ''%'' + @LastName+ ''%'''
    
    	IF (@MiddleName IS NOT NULL OR @MiddleName <> '') 
    		SET @select = @select + ' OR MiddleName Like ''%'' + @MiddleName + ''%'''
     exec sp_executesql @select , N'@FirstName VARCHAR(50) , @LastName VARCHAR(50) , @MiddleName VARCHAR(50)', @FirstName, @LastName, @MiddleName

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now