Multi conditional Where clause

1

Hello

How can we say WHERE (IF THIS1 THEN THAT1 ELSEIF THIS2 THEN THAT2 ELSE THAT3)?

Thanks!

2

Use a CASE expression:

SELECT ...
FROM ...
WHERE 1 = CASE WHEN {expression} THEN 1 ELSE 0 END
3

I don't think it can do what I want.

I wrote:
WHERE CASE WHEN Col1='Y' THEN Col2=1 ELSE Col3=1

I want to achieve:
When Col1='Y', I want to process: WHERE Col2=1
When Col1<>'Y' or when Col1=NULL, I want to process WHERE Col3=1

Any idea?

4

there is something called DYNAMIC SQL ..

5

Not sure how it relates?

6

what is dynamic sql ? read the article

in very general terms

if condition = 1
sql = 'select '+where 1

if condition = 2
sql = 'select '+where 2

7

I don't get it. 'SELECT ' is text, it cannot be a function.

8 
declare @sql4 table(Col1 char(1), Col2 int, Col3 int)

insert into @sql4
select 'Y', 1, 55 union
select 'N', 7, 1 union
select null, 78, 1


select *
  from @sql4
  where Col2 = case when Col1 = 'Y' then 1 end
     or Col3 = case when ( Col1 <>'Y' or Col1 = NULL) then 1 end
1 Like
9

You do not need the CASE expression...

Where (Col1 = 'Y' And Col2 = 1)
   Or (Col1 <> 'Y' And Col3 = 1)

You don't have to check for Col1 = NULL - because NULLs will be filtered out on the check for Col1 <> 'Y'.

Edit: Sorry - looked at his original question and you do need the check for NULL.

Where (Col1 = 'Y' And Col2 = 1)
   Or ((Col1 Is Null Or Col1 <> 'Y') And Col3 = 1)
10

That is not the structure I provided...the structure I provided is:

1 = CASE WHEN {expression} THEN 1 ELSE 0 END

If you really wanted (or needed) the above it would be:

1 = CASE WHEN Col1 = 'Y' AND Col2 = 1 THEN 1 END

But, you don't need a case expression:

where (Col1 = 'Y' And Col2 = 1)
or ((Col1 Is Null Or Col1 <> 'Y') And Col3 = 1)