Select a table dynamically - 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 2005 Forums

Transact-SQL (2005)

Select a table dynamically

Author

Topic

KDesai
Starting Member

4 Posts

Posted - 2010-03-15 : 04:31:16

Hi all,

I have 3 tables TableA, TableB and TableC.

In TableA, I have a column named TableType. Its value can be 'TableB' or 'TableC'.

Now I want to select all fields of TableA and all fields of (TableB or TableC) depending on the value of TableType. Somthing like as under :

if (TableType == 'TableB')
select TableA.*, TableB.* from TableA, TableB
else
select TableA.*, TableC.* from TableA, TableC

I do not want to use an IF Statement and want to complete this in a single query.

Any and all suggestions would be welcome.

Thanking in anticipation
Kunal

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 04:45:54

I can suggest you look for Dynamic sql..Though iam a beginner

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-15 : 04:54:20

Dynamic solution will be a good solution for you!

exec('select t1.*, t1.* from TableA t1, '+@TableType +' t2')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 04:56:38

quote:
Originally posted by senthil_nagore

Dynamic solution will be a good solution for you!

exec('select t1.*, t1.* from TableA t1, '+@TableType +' t2')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

this should be
exec('select t1.*, t2.* from TableA t1, '+@TableType +' t2')

KDesai
Starting Member

4 Posts

Posted - 2010-03-15 : 04:57:49

I am looking like having CASE statement in either FROM CLAUSE or SELECT CLAUSE to select the required table.

Is it possible to do so?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-15 : 04:59:35

quote:
Originally posted by KDesai

I am looking like having CASE statement in either FROM CLAUSE or SELECT CLAUSE to select the required table.

Is it possible to do so?

Case on from clause is not possible!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 05:00:46

select t1.*, t2.* from TableA as t1 inner join TableB as t2 on t1.key_col=t2.key_col
where t1.table_type='TableB'
union all
select t1.*, t2.* from TableA as t1 inner join TableC as t2 on t1.key_col=t2.key_col
where t1.table_type='TableC'

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-15 : 05:00:56

quote:
Originally posted by haroon2k9

quote:
Originally posted by senthil_nagore

Dynamic solution will be a good solution for you!

exec('select t1.*, t1.* from TableA t1, '+@TableType +' t2')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

this should be
exec('select t1.*, t2.* from TableA t1, '+@TableType +' t2')

haroon2k9,

Can you highlight where i did mistake? because i can't find any difference

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 05:01:08

This might be the solution because if tabletype is not equal to tableB then it should be tableC and use of alias twice is not correct as per requirement.

EXEC ('select t1.*, t2.* from TableA t1, '+ (CASE @TableType WHEN 'TableB' THEN @TableType ELSE 'TableC' END ) +' t2')

Vaibhav T

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 05:07:32

quote:
Originally posted by senthil_nagore

quote:
Originally posted by haroon2k9

quote:
Originally posted by senthil_nagore

Dynamic solution will be a good solution for you!

exec('select t1.*, t1.* from TableA t1, '+@TableType +' t2')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

this should be
exec('select t1.*, t2.* from TableA t1, '+@TableType +' t2')

haroon2k9,

Can you highlight where i did mistake? because i can't find any difference

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

see inline response as yours in red and green of mine.please correct me if iam wrong..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-15 : 05:10:05

Cool,

Thanks...

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 05:26:42

quote:
Originally posted by haroon2k9

quote:
Originally posted by senthil_nagore

quote:
Originally posted by haroon2k9

quote:
Originally posted by senthil_nagore

Dynamic solution will be a good solution for you!

exec('select t1.*, t1.* from TableA t1, '+@TableType +' t2')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

this should be
exec('select t1.*, t2.* from TableA t1, '+@TableType +' t2')

haroon2k9,

Can you highlight where i did mistake? because i can't find any difference

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

see inline response as yours in red and green of mine.please correct me if iam wrong..

That is even not upto the requirement. see my reply

Vaibhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-15 : 05:38:38

Case on dynamic sql in not the requirement!

He also mentioned table value is 'TableB'

if (TableType == 'TableB')

The Better replacement of dynamic sql for this case is madhi's solution!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 06:34:59

Okey i got it...

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources