How to use IN clause in THEN part of CASE statement without errors?
I want to use IN Clause in Then part of CASE statement. This is giving me some errors.
declare @Role varchar(10),@Region_Code varchar(100)
set @Role='AB'
SEt @Region_Code='003,002,004,005'
select * from [tableName] BM
where CASE WHEN @Role='AB'
THEN BM.[Region code] in (Select * from dbo.Split (@Region_Code, ','))
WHEN @Role='XYZ'
THEN BM.Branch_code in (Select * from dbo.Split (@Region_Code, ','))
end
Solved
Why not use IF ELSE IF statement
IF @Role='AB'
select * from [tableName] BM
where BM.[Region code] in (Select RegionCode from dbo.Split (@Region_Code, ','))
ELSE IF @Role='XYZ'
select * from [tableName] BM
where BM.[Branch_code] in (Select RegionCode from dbo.Split (@Region_Code, ','))
Also try this
select * from [tableName] BM
where ( @Role='AB' AND BM.[Region code] in (Select RegionCode from dbo.Split(@Region_Code, ',')) )
OR
( @Role='XYZ'
AND BM.Branch_code in (Select RegionCode from dbo.Split (@Region_Code, ','))
)
Comments
Post a Comment