drop. temp. table proc.

CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
AS
DECLARE @.STATEMENT VARCHAR(8000)
SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
BEGIN
EXEC(@.STATEMENT)
END
SELECT *
INTO #AA
FROM a_table
DT '#AA'
SELECT * FROM #AA--the table #AA is still existing.
How can I change the procedure to enable dropping.I think that this line is where your proc is going wrong:
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
The object name in tempdbs sysobjects table will be
#AA_________________somethinghere
Therefore, you'd have to change your = to LIKE, something like this:
@.TEMP_TABLE_NAME + '___%'
To check for an object's existence, I always try to retrieve the object's ID
using OBJECT_ID('objectname') function. If a non-null value is returned,
delete the object.
IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
I wouldn't normally recommend dynamic SQL due to the risk of a SQL injection
attack, but if it's only for your own use?
Dan.
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
> AS
> DECLARE @.STATEMENT VARCHAR(8000)
> SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME=
@.TEMP_TABLE_NAME)
> BEGIN
> EXEC(@.STATEMENT)
> END
> SELECT *
> INTO #AA
> FROM a_table
> DT '#AA'
> SELECT * FROM #AA--the table #AA is still existing.
> How can I change the procedure to enable dropping.
>|||On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:

>The object name in tempdbs sysobjects table will be
>#AA_________________somethinghere
>Therefore, you'd have to change your = to LIKE, something like this:
>@.TEMP_TABLE_NAME + '___%'
Hi Daniel,
I think that you meant to write
LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
or
LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
The _ character in a LIKE pattern will match any single character.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you.
"Daniel Doyle" wrote:

> I think that this line is where your proc is going wrong:
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME
)
> The object name in tempdbs sysobjects table will be
> #AA_________________somethinghere
> Therefore, you'd have to change your = to LIKE, something like this:
> @.TEMP_TABLE_NAME + '___%'
> To check for an object's existence, I always try to retrieve the object's
ID
> using OBJECT_ID('objectname') function. If a non-null value is returned,
> delete the object.
> IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
> I wouldn't normally recommend dynamic SQL due to the risk of a SQL injecti
on
> attack, but if it's only for your own use?
> Dan.
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> @.TEMP_TABLE_NAME)
>
>|||Yes, of course you are correct Hugo, it slippled my mind that _ is a
wildcard character.
Thanks. Dan.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vbt1g1tbdoovaq2tt15vahkm5d0qhevn4n@.
4ax.com...
> On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>
> Hi Daniel,
> I think that you meant to write
> LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
> or
> LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
> The _ character in a LIKE pattern will match any single character.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

DROP TABLE?

hi, why I can do this:
DECLARE @.table_name varchar(100)
SET @.table_name ='ABC'
DROP TABLE @.table_name
Line 3: Incorrect syntax near '@.table_name'http://www.sommarskog.se/dynamic_sql.html
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"js" <js@.someone@.hotmail.com> wrote in message
news:uDYbxIQQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
>|||js wrote:
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
Because the function requires an object literal, not a variable.
You can't even say:
Drop Table 'ABC'
You need to say:
Drop Table ABC
or
Drop Table dbo.ABC
David Gugick
Imceda Software
www.imceda.com|||js wrote:
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
I should have added that if you want to perform a dynamic DROP, you can
use dynamic SQL:
Declare @.SQL nvarchar(128)
Set @.SQL = N'Drop Table dbo.ABC'
Exec sp_executesql @.SQL
David Gugick
Imceda Software
www.imceda.com|||DROP TABLE doesn't accept a variable. What you can do is:
DECLARE @.table_name SYSNAME
SET @.table_name ='Employees'
EXEC ('DROP TABLE' + @.table_name)
Jacco Schalkwijk
SQL Server MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:uDYbxIQQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
>|||...yielding one of the weaknesses here, difficult parsing...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TABLEEmployees'.
:-)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:er1E1NQQFHA.3416@.TK2MSFTNGP10.phx.gbl...
> DROP TABLE doesn't accept a variable. What you can do is:
> DECLARE @.table_name SYSNAME
> SET @.table_name ='Employees'
> EXEC ('DROP TABLE' + @.table_name)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:uDYbxIQQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>|||Note to self: should use fixed-width fonts :)
Jacco Schalkwijk
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8OwKVQQFHA.3448@.TK2MSFTNGP10.phx.gbl...
> ...yielding one of the weaknesses here, difficult parsing...
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'TABLEEmployees'.
> :-)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:er1E1NQQFHA.3416@.TK2MSFTNGP10.phx.gbl...
>|||js wrote:
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You cannot use a variable for the table name in the command "Drop
Table."
You can, however, use dynamic SQL, which, in this case is VERY
DANGEROUS!
Use at your own risk:
DECLARE @.table_name varchar(100)
SET @.table_name ='ABC'
EXEC ('DROP TABLE ' + @.table_name)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmKZloechKqOuFEgEQJfjgCfX++bziZp74Kw
80hlHNV+y+UZ9akAoLO4
zHlTEuZb1E7oAuG7vBq0IjwD
=zRUS
--END PGP SIGNATURE--