CASE expression - 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

General SQL Server Forums

New to SQL Server Programming

CASE expression

Author

Topic

tclose
Starting Member

24 Posts

Posted - 2010-04-24 : 05:22:25

Is it possible to use a CASE expression for determining which table to use.
Example:

Declare @i tinyint = 0

while (@i < 3)
BEGIN
SELECT Product,Price FROM CASE @i
WHEN @i = 0 THEN Plumbing
WHEN @i = 1 THEN Electrical
WHEN @i = 2 THEN Lumber
@i += 1
END

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-04-24 : 05:26:58

yes it is possible.

i suggest you use dynamic query and information_schema.tables views. it would make it much easier to do this.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-24 : 05:29:09

You can use this method:

SELECT Product, Price
FROM
(SELECT Product, Price, K=0 FROM Plumbing
UNION
SELECT Product, Price, K=1 FROM Electrical
UNION
SELECT Product, Price, K=2 FROM Lumber)D
WHERE k=@i;

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-24 : 06:13:39

If you want the result to contain the data of all tables then you can use:


SELECT Product, Price
  FROM Plumbing

 UNION ALL

SELECT Product, Price
  FROM Electrical

 UNION ALL

SELECT Product, Price
  FROM Lumber

But if you want it to be form one of them you can use either this:


IF @i = 0
   SELECT Product, Price
     FROM Plumbing
ELSE IF @i = 1
   SELECT Product, Price
     FROM Electrical
ELSE IF @i = 2
   SELECT Product, Price
     FROM Lumber

Or this:


SELECT Product, Price
  FROM Plumbing
 WHERE @i = 0

 UNION ALL

SELECT Product, Price
  FROM Electrical
 WHERE @i = 1

 UNION ALL

SELECT Product, Price
  FROM Lumber
 WHERE @i = 2

tclose
Starting Member

24 Posts

Posted - 2010-04-24 : 12:13:07

I thought I might be able to do this without repeating the SELECT statement so many times through the CASE statement, but it doesn't seem to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 12:16:16

quote:
Originally posted by tclose

I thought I might be able to do this without repeating the SELECT statement so many times through the CASE statement, but it doesn't seem to work.

it wont work unless you use dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources