Condition in IN CLAUSE

Transact-SQL
1

I've to Fetch Data in Condition Wise
Like If @SGType =1 then Single Customer else Customer in Group

Like
IF @SGType =1 then Select * from AgentMast Where AgentID = @AgentID;
IF @SGType =2 then Select * from AgentMast Where AgentID IN (SELECT SpCode from Trans WHERE AppCode = @AgentID)

I'm trying to write these both conditions in 1 syntax like

Select * from AgentMast Where
AgentID In (
CASE WHEN @SGType =1 then @AgentID
ELSE
SELECT SpCode from Trans WHERE AppCode = @AgentID
END
)
Can I do this.? I'm getting an error. But how to do this.?

2

One of these queries should do the trick:

select *
  from agentmast
 where (@sgtype=1
   and  agentid=@agentid
       )
    or (@sgtype=2
   and  agentid in (select spcode
                      from trans
                     where appcode=@agentid
                   )
       )

or

select distinct a.*
  from agentmast as a
       left outer join trans as b
                    on b.spcode=a.agentid
                   and @sgtype=2
 where (@sgtype=1
   and  a.agentid=@agentid
       )
    or (@sgtype=2
   and  b.spcode is not null
       )
1 Like
3

Yeh it seems to be work. Thanking you Bro.