CODEWITHSUNDEEP
sqlsql-server
Malcolm
Malcolm

Reputation: 1807

Add IF / CASE statement inside where condition

Dont know whether we can add if or case inside where condition .

I am trying to convert a dynamic query in to simple query 

Declare @sQuery varchar(20000)
set @sQuery=''

set @sQuery ='select * from #tb_user ud'

if(@Number<>'')
set @sQuery = @sQuery + 'and upper(ud.ID) like ''' + upper(@Number) + '%'''

if(@Name<>'')
set @sQuery = @sQuery + 'and upper(ud.FName) like ''' + upper(@Name) + '%'''

Exec(@sQuery )

Note :

The below given query is just an example i have put off where i am not getting how to handle the if condition in simple query.(Actual query has a lot of things)

Thanks in advance for any help

Upvotes: 1

Views: 152

Answers (2)

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195982

You will have to group each test in a OR construct with the parameter being empty (or whatever check -inverted- you do in the if)..

SELECT 
    * 
FROM 
    #tb_user ud
WHERE
    (@Number = '' OR upper(ud.ID) like upper(@Number) + '%')
    AND
    (@Name = '' OR upper(ud.FName) like upper(@Name) + '%')

thanks go to @bartosz for spotting an error in the initial answer logic

Upvotes: 1

Bartosz Klimek
Bartosz Klimek

Reputation: 565

Seems that you only want to use a specific filter (@Number or @Name) if it's not empty:

SELECT 
    * 
FROM 
    #tb_user ud
WHERE
    (@Number = '' OR upper(ud.ID) like upper(@Number) + '%')
    AND
    (@Name = '' OR upper(ud.FName) like upper(@Name) + '%')

Upvotes: 2

Related Questions