stored procedure: pass table as parameter

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

Transact-SQL (2000)

stored procedure: pass table as parameter

Author

Topic

kevin128
Starting Member

4 Posts

Posted - 2006-04-20 : 17:09:52

Hi Everybody,

When I ran following codes I got an error message "Must declare the table variable "@table_name"".

create procedure spCount @table_name varchar(40) as
begin
declare @count int;
select @count = count(*) from @table_name
print @count
end
GO
declare @table_name varchar(40)
set @table_name = 'table1'
exec spCount @table_name
GO

Is there a way to make it work? (Since the actual codes are more complicated, I don't want to use a exec('...') type solution.) Any suggestions would be greatly appreciated.

Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-20 : 17:14:56

Dynamic SQL is your only solution. But it is a very bad thing to do here. Why do you need to pass the table name in?

Tara Kizer
aka tduggan

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-04-20 : 17:18:33

sorry buddy, if you're going to PASS in the table name, only to select from it, you have no choice but to create DYNAMIC SQL and use: sp_executesql
and like the cutie Tara says... bad idea :(

kevin128
Starting Member

4 Posts

Posted - 2006-04-21 : 09:50:30

Hi tkizer and DBASlut,

Thank both of you for the comments and suggestions.

Kevin

quote:
Originally posted by DBASlut

sorry buddy, if you're going to PASS in the table name, only to select from it, you have no choice but to create DYNAMIC SQL and use: sp_executesql
and like the cutie Tara says... bad idea :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources