none
sql 2008 query,Changing where clause condition based on Parameter selected

    Question

  • Hi All
    I am  using sql server 2008 to write my query

    i have 2 parameter in my stored procedure
    1-@designation
    2-@emp_id

    my query in stored procedure is  i want to insert data into #temp table from my dimension table
    where my @emp_id changes based on parameter @designation passed


    i have 10 column in dimension table (a,b,c,d,us_name,us_id,aus_name,aus_id,Amr_name,Amr_id)

    example of my query in stored procedure


    insert into #temp
    select a,b,c
    from dimension
    where
     
    now if us_name is passed in designation then in where condition i need to used
    (us_id=@emp_id
    and d is not null)

    if aus_name is passed in designation then in where condition i need to used (aus_id=@empid
    and d is not null)

    if Amr_name is passed in designation then in where condition i need to used( Amr_id=@empid
    and d is not null)

    but i dont wont to used below logic because in actual scenario my select query is very lengthy and i cant repeat the code  like

     if (@designation ='us_name')
      begin

     insert into #temp
     select a,b,c
     from dimension
     where
     us_id=@emp_id
     and d is not null

      end
     
     if(@designation='aus_name')
     begin
     insert into #temp
     select a,b,c
     from dimension
     where
     aus_id=@empid
     and d is not null

      end ......not like this..

    what other approach i should used to change my where condition
    please help

    • Moved by Tom PhillipsModerator Wednesday, January 12, 2011 3:09 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, January 12, 2011 1:16 PM

Answers

All replies

  • The most efficient method will be the conditional IF statements since these are fundamentally different queries.  You could use a complex WHERE clause with ORed conditions but the query plan will likely be sub-optimal. 

    One method is a UNION ALL of the 2 mutually exclusve selects:

    INSERT INTO #temp
    	SELECT a,b,c
    	FROM dbo.dimension
    	WHERE
    		@designation ='us_name'
    		AND us_id=@emp_id
    		AND d IS NOT NULL
          UNION ALL
    	SELECT a,b,c
    	FROM dbo.dimension
    	WHERE
    		@designation ='aus_name'
    		AND aus_id=@empid
    		AND d IS NOT NULL;
    

    Another approach is to build the needed WHERE clause dynamically using a parameterized sp_executesql excute.  See Erland's article (http://www.sommarskog.se/dynamic_sql.html) for a detailed dynamic SQL description. 

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Wednesday, January 12, 2011 1:38 PM
  • In addition to the above suggestions, the following page has dynamic SQL examples including dynamic SQL stored procedures:

    http://sqlusa.com/bestpractices/dynamicsql/

     


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    • Marked as answer by Mushtaq308 Thursday, January 13, 2011 5:09 AM
    Wednesday, January 12, 2011 3:16 PM

  • INSERT INTO #temp
    SELECT a ,b, c
    FROM Dimension
    WHERE d IS NOT NULL
     AND((us_id = @in_emp_id
          AND @in_designation = 'us_name')
         OR (aus_id = @in_emp_id
          AND @in_designation = 'aus_name'));

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Proposed as answer by e.herlich Tuesday, June 05, 2012 5:39 PM
    Wednesday, January 12, 2011 6:05 PM
  • HI Dan
    Thanks for your reply
    But in actual scenario i have very big select statement and in  @designation i have 10 designation to pass

    So if i use Union all ,then i will have to wirte the same select statement 10 times,

    as i am working on client location so i clear instruction not to repeat the data and they want change only WHERE condition as per designation,and they want select statement to be STATIC(should not be repeated again for every designation)

    Thursday, January 13, 2011 5:02 AM
  • In that case, dynamic SQL is probably the best solution.  The links that Kalman and I posted should help you with that.  Additionally, I suggest you read Erland' stored procedure security article (http://www.sommarskog.se/grantperm.html) if users are not to access underlying tables directly.  Personally, I prefer certificate method.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Thursday, January 13, 2011 12:36 PM correct typos
    • Marked as answer by Mushtaq308 Monday, January 17, 2011 7:47 AM
    Thursday, January 13, 2011 12:34 PM