Pass Table name as Parameter to procedure

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)

Pass Table name as Parameter to procedure

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2005-10-23 : 14:44:43

Hey Guys,

I've been wondering if there is way to achieve something like this:

create procedure dbo.getTable
(
@tableName varchar(50)
)
AS
SELECT * FROM @tableName

I want to be able to pass the table name in to a stored procedure dynamically, but the above procedure wont compile, what am I doing wrong...Please help

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-23 : 15:29:22

It can be done like this.
CREATE PROCEDURE dbo.getTable
(
@tableName varchar(50)
)
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM ' + @tableName

EXEC(@SQL)

.....but why do you want to do this?

Duane.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-23 : 17:55:48

dynamic sql like this is usually a very bad idea. It can cause performance problems, or security issues. Avoid this type of thing at all costs.

-ec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 00:56:59

More on Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2005-10-24 : 09:23:12

Thank you guys, guess I was trying for a shortcut, passing in the table name instead of creating multiple stored procedures or editing the stored procedures and changing table names each time i wanted to modify a different table.

Thanks for the advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources