how to write a parameter to pull a table name

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

SQL Server Development (2000)

how to write a parameter to pull a table name

Author

Topic

jamlogg
Starting Member

10 Posts

Posted - 2010-08-17 : 10:22:34

I have a scenario where I have 25 tables and are not linked. All the tables have same tpye of fields/columns with different data for each table. So the user has to input the table name e.g C2, R3, or R5 in order to pull data. So my qustion is, how do you write the parameter in sql to enable the user to input a table name to pull data based off that particular table only. I have use this query as an embedded sql for a report. I have one query, but does not pick up the table name in the prompt, so if somebody could help in, it would be appreciated.

Select col1, col2, col3,...coln, 'C1' from C1Table
where 'C1' = {table_prompt} union
select col1, col2, col3,....coln, 'R1' from R1Table
where 'R1' = {table_prompt}
union
select col1, col2.......

K.G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 11:00:59

you need to use dynamic sql for that

like

'SELECT columns.. FROM ' + @tablename

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

jamlogg
Starting Member

10 Posts

Posted - 2010-08-17 : 12:24:54

Ok. Why is there a + infront of @tablename? and so @tablename should be hard coded. I am not a sql expert but what do you suggest the coding for @tablename should be, an example will do and appreciate your reply.

K.G

jamlogg
Starting Member

10 Posts

Posted - 2010-08-17 : 12:56:40

sorry. I understood the sql, the + confused me a little.

select * from {?tablename}

Thanks.

K.G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 13:04:45

yeah...but whats the sql you're using? i've never seen this syntax {?tablename}

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

jamlogg
Starting Member

10 Posts

Posted - 2010-08-17 : 17:28:13

its a 'sql expressions' syntax

K.G

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-17 : 21:47:10

Are you using SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources