Using CASE in a FROM statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

Using CASE in a FROM statement?

Author

Topic

davenims
Starting Member

21 Posts

Posted - 2009-01-21 : 07:17:38

Hello,

Is it possible to use CASE WHEN in a FROM statement?

So I could do something like the following in a UDF:


RETURN (
     SELECT SomeField 
     FROM 
     CASE WHEN @TableName = 'a' THEN TableA ELSE TableB END
)

The above syntax doesn't work, just wondering if there is another way to do the same thing. Cheers!

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-21 : 07:31:53

Try this

declare @var varchar(555),@tablename varchar(555),@str varchar(666)
select @var = 'a'

if(@var='a')
select @tablename='tablea'
else
select @tablename='tableb'

select @str = 'select id from '+@tablename

exec(@str)

Jai Krishna

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-01-21 : 08:24:58

[code]IF @Tablename = 'a'
BEGIN
RETURN(SELECT SomeField from TableA)
END
ELSE
BEGIN
RETURN(SELECT SomeField from TableB)
END[/code]

Really no need for dynamic SQL ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-21 : 08:41:34

One more way:

RETURN 
(
     SELECT SomeField 
     FROM TableA
     Where @TableName = 'a'
     union all
     SELECT SomeField 
     FROM TableB
     Where @TableName = 'b'
)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

davenims
Starting Member

21 Posts

Posted - 2009-01-21 : 08:47:24

Thanks all.

I was trying to avoid repeating the statement if possible, because it's quite a big one, but I guess that's not going to work.

Also I'd rather avoid dynamic SQL if poss, but thanks anyway!

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-01-21 : 09:22:02

There's no dynamic SQL with Harsh's or my solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-21 : 09:33:29

quote:
Originally posted by davenims

Thanks all.

I was trying to avoid repeating the statement if possible, because it's quite a big one, but I guess that's not going to work.

Also I'd rather avoid dynamic SQL if poss, but thanks anyway!

Not possible without dynamic sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources