> Is it possible in a SQL Server Stored Procedure to have
> dynamic table names?
>
> I'm currently trying to;
> Create Procedure update_pages (@tablename varchar(255),
> @rowlist varchar(255))
> as
> begin
> update [@tablename] inner join Central_Pages on
> [@tablename].ID=Central_Pages.ID
> set [@tablename].Title=Central_Pages.Title,
> [@tablename].Body=Central_Pages.Body
> where [@tablename].ID in (@rowlist)
> end
> GO
>
> When I check the syntax I get "Error 156: Incorrect syntax
> near the keyword 'inner'."
>
> Please tell me I'm not going completely mad and that it'll
> let me do this
>
> The problem is that I've got to update up to 200 tables and
> only specific pages, I don't specifically want to do this
> via CF (for obvious reasons)
You can do what you want, but not as simply as this.
Typically, when you're building SQL on the fly, you'll have to build a
string, then use EXECUTE (in SQL 6.5) or sp_executesql (in SQL 7 or 2000) to
execute the string.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists