shamim
shamim

Reputation: 6770

Expression in where clause for merge condition

Table have one column named Type , is there any way to merge two condition in where close.

base table

select Amount,Type,IsVisibleToCustomer,Status from LiveCustomerTransaction


declare @Type int=0;

--@Type=1

select * from LiveCustomerTransaction where Type in (1,4,5)

--@Type=2,3

select * from LiveCustomerTransaction where Type=@Type

How to merge Type 1 and 2,3 condition in where close when @type is 1 then only 1,4,5 types are selected, when type is 2 or 3 then 2 or 3 are selected.

Upvotes: 0

Views: 56

Answers (2)

Peter B
Peter B

Reputation: 24147

I believe this is what you are looking for:

select * from LiveCustomerTransaction
where ( @Type=1        and Type in (1,4,5) )
or    ( @Type in (2,3) and Type in ( 2,3 ) )

In this, 2 and 3 are interchangable (so Type is not necessarily the same as @Type, both should be either 2 or 3). If you meant it a bit more strict then it becomes this:

select * from LiveCustomerTransaction
where ( @Type=1        and Type in (1,4,5) )
or    ( @Type in (2,3) and Type=@Type      )

Upvotes: 1

TriV
TriV

Reputation: 5148

You could try the following query

select * 
from LiveCustomerTransaction 
WHERE (@Type=1 and Type in (1,4,5))
      OR (@Type in (2,3) and Type = @Type)

Upvotes: 0

Related Questions