CASE with IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CASE with IN

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-07-20 : 14:09:43

What is the way to make the following code work?

What it's trying to do is a simple existence test, with the catch that the Type column should be able to include several choices based on the @ClmType variable.

IF EXISTS(SELECT * FROM TableA A INNER JOIN TableB B ON B.Code = 

A.Code WHERE A.Id = @PatID AND B.Code <> @CodeX AND NEICID IS NOT NULL 

AND LEN(NEICID) > 0 AND Type IN (CASE WHEN @ClaimType = 1 THEN (6,7) ELSE (0,1,8) END))

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-07-20 : 14:20:01

One possible solution: a user-defined function in the Type IN(fn_example (@ClaimType)) that returns a table listing either 6 and 7 or 0, 1, and 8. Ugh, messy.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-20 : 15:52:10

Perhaps this:


ELECT * 
FROM TableA A 
INNER JOIN TableB B ON B.Code = A.Code 
WHERE A.Id = @PatID AND B.Code <> @CodeX AND NEICID IS NOT NULL 
AND LEN(NEICID) > 0       
AND    (
       @claimType = 1 and Type in (6,7)
       OR
       isNull(@claimType,-1) != 1 and Type in (0,1,8)
       )

Be One with the Optimizer
TG

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2011-07-20 : 18:17:51

Great! Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources