SELECT fld1, fld2, fld3, WHERE CASE.....

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 2000 Forums

Transact-SQL (2000)

SELECT fld1, fld2, fld3, WHERE CASE.....

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-08-09 : 16:05:09

Hey all-

I need to do a little dynamic sql within a SP.

SELECT ER_DEPT FROM E_MASTER WHERE

CASE
WHEN @inTYPE='MA'
ER_ACCOUNT LIKE '509%'
END
WHEN @inTYPE='LA'
ER_ACCOUNT LIKE '501%'
END
ELSE
ER_ACCOUNT LIKE 'PROV%'
END

How can I do this? I am passing a 2 char variable to the sp that tells it what kind of condition to use.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 16:10:36

SELECT ER_DEPT FROM E_MASTER WHERE
(@inTYPE='MA' And ER_ACCOUNT LIKE '509%')
or
(@inTYPE='LA' And ER_ACCOUNT LIKE '501%')
or
(@inType not in ('MA','LA') and ER_ACCOUNT LIKE 'PROV%')

Corey

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 16:11:44

I'd suggest:

SELECT ER_DEPT
   FROM E_MASTER
   WHERE ER_ACCOUNT LIKE CASE
      WHEN @inTYPE='MA' THEN '509%'
      WHEN @inTYPE='LA' THEN '501%'
      ELSE                   'PROV%'
   END

-PatP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 16:12:17

I think this should do it:

WHERE (@inTYPE = 'MA' AND ER_ACCOUNT LIKE '509%)
OR (@inTYPE = 'LA' AND ER_ACCOUNT LIKE '501%)
OR (ER_ACCOUNT LIKE 'PROV%)

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 16:19:29

Tara - yours would show all (ER_ACCOUNT LIKE 'PROV%) regardless of @inType

I wonder if thats what [s]he wants

Corey

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-08-09 : 16:32:31

Sorry about that. Let me clear it up a bit.

If @inTYPE = 'MA' ONLY GET 509 ACCOUNTS
IF @inTYPE = 'LA' ONLY GET 501 ACCOUNTS

Thanks
Doug

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 16:33:48

then my solution or PatP's solution should work...

Corey

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-08-09 : 16:53:06

Thanks,
What If I wanted not to include. Let me explain a little better.

SELECT DISTINCT ER_DEPT
FROM E_MASTER
WHERE ER_ACCOUNT CASE
WHEN @inTYPE='MA' THEN LIKE '509%'
WHEN @inTYPE='LA' THEN LIKE '501%'
WHEN @inTYPE='OV' THEN LIKE 'PROV%'
WHEN @inTYPE='OD' THEN NOT LIKE 'PROV%' OR ER_ACCOUNT NOT LIKE '501%' OR ER_ACCOUNT NOT LIKE '509%'
END

Can I do this? The last one is my "catch-all". Should I do something like this...

DECLARE @SQL VARCHAR(500)

SELECT @SQL = CASE
WHEN @inTYPE='MA' THEN 'SELECT THIS THAT WHERE ER_ACCOUNT LIKE'+ CHAR(39) + '501%' + CHAR(39)
WHEN @inTYPE='LA' THEN 'SELECT THIS THAT WHERE ER_ACCOUNT NOT LIKE'
END

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 18:20:45

Then it gets more ugly, like:

SELECT DISTINCT ER_DEPT
   FROM E_MASTER
   WHERE ('MA' = @inTYPE AND ER_ACCOUNT LIKE '509%')
      OR ('LA' = @inTYPE AND ER_ACCOUNT LIKE '501%')
      OR ('OV' = @inTYPE AND ER_ACCOUNT LIKE 'PROV%')
      OR ('OD' = @inTYPE
         AND ER_ACCOUNT NOT LIKE 'PROV%'
         AND ER_ACCOUNT NOT LIKE '501%'
         AND ER_ACCOUNT NOT LIKE '509%')

Just an observation, but this leads to murderous execution plans, so it might be more practical to separate the cases into distinct SELECT statements and use a UNION ALL to join them back together.

-PatP

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-08-10 : 09:09:57

Thanks Pat. I am going to look into the UNION ALL that you suggested. Thanks again.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-10 : 10:42:07

Sorry:

SELECT DISTINCT ER_DEPT
   FROM E_MASTER
   WHERE 'MA' = @inTYPE
      AND ER_ACCOUNT LIKE '509%'
UNION ALL SELECT DISTINCT ER_DEPT
   FROM E_MASTER
   WHERE 'LA' = @inTYPE
      AND ER_ACCOUNT LIKE '501%'
UNION ALL SELECT DISTINCT ER_DEPT
   FROM E_MASTER
   WHERE 'OV' = @inTYPE
      AND ER_ACCOUNT LIKE 'PROV%'
UNION ALL SELECT DISTINCT ER_DEPT
   FROM E_MASTER
   WHERE 'OD' = @inTYPE
      AND ER_ACCOUNT NOT LIKE 'PROV%'
      AND ER_ACCOUNT NOT LIKE '501%'
      AND ER_ACCOUNT NOT LIKE '509%'

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources