How to use if in where clause

[pavikavi]

Hi

I have to create a procedure based on the input parameter i have to append the conditon in where clause for ex:

Select * from table1
where if(status =live)
status not in ...
else status in ...

Can some one please help me out on this

pavithra

 

[VDavid]

pavikavi

Are there more conditions?
If it's only one why don't you use the IF statement before the query.
Ex.

if status=live then
 Select * from table1 where status not in ...;
else 
 Select * from table1 where status in ...;
end if

Post more info so we can help you

Hope this helps,
David.

 

[pavikavi]

i have more condition after that statement

Can you please provide me with more hlp

Thanks
pavithra

 

[VDavid]

How does the query goes?
I'm asking to try to understand your question.
And as I said before, why don't you use the IF before the query, and if you have more than one just make an inner IF, something like:

if status=live then
 if parameter2=something then
  if parameter3=something then
   Select * from table1 where status not in ...;
  else 
   Select * from table1 where status in ...;
  end if
 else
  //some query
 end if
else
 //some query 
end if

You can also use the CASE statement

CASE
WHEN search-condition
THEN expression,...
[ ELSE expression ]
END

Maybe I'm not understanding your question, try to be more specific please

Hope this helps,
David

 

[leyka]

Try this:

Select * from table1 
where 
((status = live) and (status not in ...))
or ((status <> live) and (status in ...))

 

[nikhilparchure]

Does the "if" in SQL Server has a then????
pls correct me if I am wrong

Regards
Parchure Nikhil

 

[leyka]

>>Does the "if" in SQL Server has a then????
>>pls correct me if I am wrong

T-SQL: no 'then', no 'end if' after 'if'
Watcom-SQL: needs 'then' and 'end if' after 'if'

MS SQL Serever: T-SQL only
ASA: T-SQL or Watcom-SQL