SQLTeam.com | Weblogs | Forums

How to use decode function in sql server in where condition


#1

Hi,

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.

decode(p_col,'','1',DATA_COLUMN)=decode(p_col,'','1',p_col)

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


#2

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

https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-choose-transact-sql


#3

Hi,

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.


#4

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