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

Answer 1

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/

Answer 2

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

Answer 3

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

Answer 4

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)

Answer 5

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/