Solved

Search Query Dynamic Vs Static - SQL Server 2008 / 2012

Posted on 2014-03-27
2
690 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 143

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

840 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