SQLTeam.com | Weblogs | Forums

How to use decode function in sql server in where condition


I have to implement the below decode function in sql server. In which, if the variable is empty, then all the rows to be retrieved by the query.


if the variable is empty the query ==> select * from TABLE where 1=1

ELSE select * from TABLE where DATA_column=p_col

Can you kindly let me know how can we achieve it in sql server

The CHOOSE() function is the equivalent of DECODE():



I have used below decode function in oracle, in which if we get the parameter value (p_lob) then it will retrieve the corresponding rows. If the parameter p_lob is empty, then it returns all the rows.

select * from table where decode(p_lob,'','1',LOB)=decode(p_lob,'','1',p_lob);

Is there any function avaiable in sql server to achieve it.. Kindly let me know is there anyway to achieve it.

I have used isnull() function and resolved the issue. Thanks