CODEWITHSUNDEEP
sqlsql-serversql-server-2008
rexroxm
rexroxm

Reputation: 878

Different where conditions through if and else in sql

I am using the following query:

if(@usertype = 'all')
begin
    select * 
    from tbllogin a 
    left join tblUserType_Master b on a.typeid = b.id 
    where (b.user_type = 'agent' or b.user_type = 'branch') ; 
end
else if(@usertype = 'branch')
begin
    select * 
    from tbllogin a 
    left join tblUserType_Master b on a.typeid = b.id 
    where b.user_type = 'branch' ; 
end
else if(@usertype = 'agent')
begin
    select * 
    from tbllogin a 
    left join tblUserType_Master b on a.typeid = b.id 
    where b.user_type = 'agent' ; 
end

This works perfectly fine but the only difference between the three queries is the where condition. Is there any way that I can store the value of where condition in a variable and just add it to the common part.

Upvotes: 1

Views: 42

Answers (2)

Mansoor
Mansoor

Reputation: 4192

Use CASE Statement in WHERE Clause to get your result :

SELECT * FROM tbllogin a 
LEFT OUTER JOIN tblUserType_Master b ON a.typeid=b.id 
WHERE ( @usertype='all' AND b.user_type IN ('agent','branch') ) OR 
      b.user_type = CASE WHEN @usertype='branch' THEN 'branch' 
                         WHEN @usertype='agent' THEN 'agent' END

Upvotes: 1

Philip P.
Philip P.

Reputation: 1212

You already have a variable with the value as far as I can see. Why not simply use it:

select
    *
from
    tbllogin a
    left join tblUserType_Master b on a.typeid=b.id
where
    b.user_type = @usertype or (@usertype='all' and b.usertype in ('branch','agent'))

I would also like to point out a couple of things: you are using left joins but comparing to the right-side table, which will eliminate all records from the tbllogin that do not have a match in the tblUserType_Master anyway.

You can do the following instead, it will surely perform better:

select
    *
from
    tbllogin a
    inner join tblUserType_Master b on a.typeid=b.id
where
    b.user_type = @usertype or (@usertype='all' and b.usertype in ('branch','agent'))

Also, I strongly recommend against using select *, listing the columns explicitly is a recommended path.

Upvotes: 1

Related Questions