Solved

Search Query Dynamic Vs Static - SQL Server 2008 / 2012

Posted on 2014-03-27
2
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

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…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

733 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