Select data from @table - 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 2000 Forums

SQL Server Development (2000)

Select data from @table

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2005-02-17 : 14:20:24

How to select data from @table, here @table is parameter.

I use:

Create Proc ProcSelect
@table varchar(30)
As
Select * from @table

It gives me an error.
Server: Msg 137, Level 15, State 2, Procedure ProcSelect, Line 4
Must declare the variable '@table'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:25:24

Why do you want to do this? This is so bad for performance and security reasons. Why even bother with stored procedures if you are going to write code like this?

Tara

TAS
Yak Posting Veteran

65 Posts

Posted - 2005-02-17 : 14:29:04

I have a program to retreive data from different tables. Users need to select any data they want from different tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:31:45

You should have a different stored procedure per table that you want to select data from.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 14:34:51

How about a UDF?

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:35:51

How would a UDF help?

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 14:48:25

Because I have read in BOL on the differences between sprocs and udfs that "if you want to invoke a stored procedure directly from a query, repackage the code as a user-defined function."TAS already said "I have a program to retreive data from different tables. Users need to select any data they want from different tables."
That's why I made that suggestion.

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:52:19

UDFs will not help TAS in this situation. UDFs certainly have their use but not here.

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-18 : 04:46:07

Create Proc ProcSelect
@table varchar(30)
As
exec ('Select * from '+@table)

Madhivanan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-19 : 02:20:26

Ugh. I can't believe you posted that. The whole reason why we didn't show him that is dynamic SQL should not be used for this type of problem, the problem being lazy programming.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources