TSQL, Dynamic Where Clause

Question

I Have a SQL statement, with Different 'Where clauses' given different Parameters values passed to a Stored Procedure.

I do not want to write 5 different SQL statements for each passed level. I thought of just 1 SQL STATEMENT with a CASE STATEMENT.

Alternatively, i could consider using dynamic SQL but that is a bit messy.

Below is my example (not in working order) - Any suggestions plse.

This is MS SQL Server 2012

Declare @Level int = 1;

SELECT *

  FROM [HR_MIS_STAGING].[dbo].[XXXXX_Promotions_201510_201610]

where

case @Level when 1 then 
    appt_no = '01';
case @Level when 2 then 
    org = '15';
case @Level when 3 then 
    race = '1';
case @Level when 4 then 
    Gender= 'M';
case @Level when 5 then 
    Prov= 'Nat';

END

[This](https://stackoverflow.com/a/10260297/92546) answer demonstrates how to use a `case` _expression_ in an `on` or `where` clause. Performance may be an issue with precompiled statements due to parameter sniffing. — HABO, May 29 '17 at 16:24

score 3 · Accepted Answer · answered May 29 '17 at 14:53

3

Do something like this:

where (@Level = 1 and appt_no = '01') or
      (@Level = 2 and org = '15') or
      (@Level = 3 and race = '1') or
      (@Level = 4 and Gender = 'M') or
      (@Level = 5 and Prov = 'Nat')

Note: If you use dynamic SQL, then the resulting query is more likely to make use of available indexes.

answered May 29 '17 at 14:53

Gordon Linoff

1,242,037
58
646
786

Your solution dawned on me this morning - Appreciated! – Stefan May 30 '17 at 08:13

ppijnenburg · Answer 2 · 2017-05-30T06:30:41.753

This should work, I made the sql dynamic as suggested above: By setting the where clause first with a case statement you can insert it in the sql. Note the else; I've set it to show everything when @level doesn't match any given case. Just change it to '1 = 0' if you don't want to show anything when the case doesn't match

DECLARE @level int
SET @level = 4

DECLARE @where nvarchar(max)
DECLARE @sql nvarchar(max)
; 
SET @where = CASE 
  WHEN @level = 1 THEN 'appt_no = ''01'''
  WHEN @level = 2 THEN 'org = ''15'''
  WHEN @Level = 3 THEN 'race = ''1'''
  WHEN @Level = 4 THEN 'Gender= ''M'''
  WHEN @Level = 5 THEN 'Prov= ''Nat'''
  ELSE '1 = 1' 
END 

SET @SQL = 'SELECT *
  FROM [HR_MIS_STAGING].[dbo].[XXXXX_Promotions_201510_201610]
where '+ @where + ''

exec (@sql)

TSQL, Dynamic Where Clause

2 Answers2