Passing Table name into Stored Procedure

Last Post 13 Sep 2006 12:34 PM by whynot. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tcarnes
New Member
New Member

--
12 Sep 2006 10:56 AM
I am trying to create a Stored Procedure that will update respective tables based on the table name variable passed in. This is what I've tried that isn't working:

declare @Text varchar(255)
declare @Table varchar(20)
declare @ID int

set @Text = 'Submitted'
set @Table = 'status'
set @ID = 4

Insert @Table ([ID], [Text]) VALUES (@ID, @Text)

I then get the error:
Msg 1087, Level 15, State 2, Line 10
Must declare the table variable "@Table".

Now do I declare the table variable "@Table"

Terry
whynot
New Member
New Member

--
13 Sep 2006 12:34 PM
People like to think the @Text will be replaced with the value in it. Well, It is not, can't be done.
Only danymic query can do it. First construct the statement:

declare @sql varchar(200)

set @sql = 'Insert @Table ([ID], [Text]) VALUES (@ID, ' + @Text + ')'

then excute the query:

exec (@sql)
SQLUSA
New Member
New Member

--
01 Sep 2008 02:18 AM
quote:

Originally posted by: tcarnes
I am trying to create a Stored Procedure that will update respective tables based on the table name variable passed in. This is what I've tried that isn't working:

Terry


For production, the best to write hard-wired, table-specific stored procedure due to performance and maintenance consideration.

While dynamic SQL appears to be "object-oriented", it is not, more like compiler preprocessing, also opens the sproc to SQL injection attacks.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
You are not authorized to post a reply.

Acceptable Use Policy