Solved

Search Query Dynamic Vs Static - SQL Server 2008 / 2012

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

831 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