Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Search Query Dynamic Vs Static - SQL Server 2008 / 2012

Posted on 2014-03-27
2
Medium Priority
?
716 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

609 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