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)