Passing a table name into a stored procedure as a variable

[EBrownSQL]

I have to populate tables monthly for a printing job. Each table is populated using the same code. Is there a way to pass the table name into a stored procedure and have it populated. When I try to just pass it into the stored procedure I get an error, because the code does not recognize the variable name as a table. I would appreciate any suggestions.

 

[sunila7]

Hi,

u will have to create a dynamic SQL query to this
something like this...

create proc procname
@tablename varchar(20)
AS
Declare @SQL varchar(200)
Set @SQL = 'Insert into ' + @tablename + ' values(a,b,c,d)'
exec(@SQL)

Sunil

 

[tlbroadbent]

The reason dynamic SQL is needed is because SQL cannot use variables for object or column names. This is stated explicitly in SQL books online. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.