You can't use variables in SQL server to represent objects (tables,
view. etc) -- you can only use them to represent columns. If you need
to use one to represent an object, then you have to either use exec()
the sp_executeSQL stored procedure or an ad-hoc query in CFML using a
CF variable for the table name.
exec syntax looks like this:
exec('drop table ' + @crDataTable)
or
exec sp_executeSQL N'drop table ' + @crDataTable'
The advantage of sp_executeSQL is that there's a version under .dbo.
in each catalog on your server which can be used to perform certain
tasks which SQL Server limits to not being allowed to specify a
catalog name (in other words,"create view catalog.dbo.vMyView ..."
will fail, but "catalog.dbo.sp_executeSQL N'create view '..." works
fine).
hth
> Yep, if I change 'DROP TABLE' to 'PRINT', I get
> '_crData4012'.
> The error message just says 'Line 8: Incorrect syntax near
> '@crDataTable'.'
> -----Original Message-----
> From: Robertson-Ravo, Neil (RX)
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 05, 2005 7:41 AM
> To: CF-Talk
> Subject: RE: SQL Drop Table using Variable?
> Have you outputted @crDataTable to see what the values is?
> Also, what is the syntax error?
> -----Original Message-----
> From: Jeff Chastain [mailto:[EMAIL PROTECTED]
> Sent: 05 May 2005 13:35
> To: CF-Talk
> Subject: OT: SQL Drop Table using Variable?
> Sorry for the off-topic, but I am having a bit of a query
> issue. I have a
> dynamically created table that is named ' _crData123 '
> where the 123 is some
> numeric identifier. Now, I need to be able to drop this
> table. The
> following query finds the table name and stores that name
> in a variable, but
> when I attempt to drop it via the variable, I get a syntax
> error. Any
> suggestions on what is wrong with this or another way to
> do this in SQL
> Server?
> ----------
> DECLARE @crDataTable char(30)
> SELECT @crDataTable = name
> FROM sysobjects
> WHERE xtype='U' AND name LIKE '[_]crData%'
> DROP TABLE @crDataTable
> ----------
> Thanks
> -- Jeff
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:205645
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
