Properly test for existence of a temporary stored procedure

How do I test for the existence of a temporary stored procedure (or get its
id)?
Testing for existence of a regular stored procedure is easy:
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp' and id = ... and
objectproperty( ... isprocedure) ...)
Testing for existence of a temporary table is easy:
if object_id('tempdb.dbo.#temp') <> 0
And testing for existence of a temporary stored procedure seems easy, too:
if object_id('tempdb.dbo.#sp') <> 0
Only, if a table exists in tempdb that has the name #sp, the object ID
returned is that of the table, not the sp.
This *probably* won't cause me a problem, but I don't like writing code that
relies on probablies. How can I do a simple test for existence of a temporar
y
stored procedure so I can drop it before recreating it?
Thanks,
Erik
Elbisrever isn'''t.
"Erik Eckhardt" wrote:

> How do I test for the existence of a temporary stored procedure (or get it
s
> id)?
> Testing for existence of a regular stored procedure is easy:
> IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp' and id = ... and
> objectproperty( ... isprocedure) ...)
> Testing for existence of a temporary table is easy:
> if object_id('tempdb.dbo.#temp') <> 0
> And testing for existence of a temporary stored procedure seems easy, too:
> if object_id('tempdb.dbo.#sp') <> 0
> Only, if a table exists in tempdb that has the name #sp, the object ID
> returned is that of the table, not the sp.
> This *probably* won't cause me a problem, but I don't like writing code th
at
> relies on probablies. How can I do a simple test for existence of a tempor
ary
> stored procedure so I can drop it before recreating it?
> Thanks,
> Erik
> --
> Elbisrever isn'''t.
Erik,
Can you use something like this:
IF EXISTS (
SELECT 1 FROM tempdb.dbo.sysobjects
WHERE name like '#sp____%' -- All temp objects start with the
name followed
-- by a bunch of underscores and a
unique number.
AND xtype = 'P' -- Stored procedure
)
PRINT 'Stored Proc #sp exists'
ELSE
PRINT 'Stored Proc #sp DOES NOT exist'|||"LA_DBA" wrote:

>
> "Erik Eckhardt" wrote:
>
> Erik,
> Can you use something like this:
> IF EXISTS (
> SELECT 1 FROM tempdb.dbo.sysobjects
> WHERE name like '#sp____%' -- All temp objects start with the
> name followed
> -- by a bunch of underscores and a
> unique number.
> AND xtype = 'P' -- Stored procedure
> )
> PRINT 'Stored Proc #sp exists'
> ELSE
> PRINT 'Stored Proc #sp DOES NOT exist'
>
Dear LA_DBA,
That won't work because it will only tell me if someone on the server
somewhere in some database has a stored procedure by that name existent. It
doesn't tell me for my current connection/session.|||IF OBJECT_ID('tempdb..#tbl') IS NOT NULL ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Erik Eckhardt" <egamlaprov@.liamtoh.moc(reverse each section)> wrote in mess
age
news:EFB75273-3DA1-4C04-B6D5-7F3EB9942885@.microsoft.com...
> "LA_DBA" wrote:
>
> Dear LA_DBA,
> That won't work because it will only tell me if someone on the server
> somewhere in some database has a stored procedure by that name existent. I
t
> doesn't tell me for my current connection/session.|||You can supply a second parameter to object_id, 'P'
for stored procedure, 'U' for table etc.
if object_id('tempdb.dbo.#sp') <> 0
becomes
if object_id('tempdb.dbo.#sp','P') <> 0|||Mark,
Thanks for the info on this undocumented parameter (at least in SQL Server
2000 BOL). The interesting thing is, when a temp table exists by the same
name, object_id('tempdb.dbo.#blah', 'P') returns a NULL. So rather than 'P'
specifying an object of type P, it simply requests that whatever object it
finds first... if it's NOT type 'P', return NULL instead.
I guess the only solution is just to attempt the drop and trap for and
discard any error about nonexistence.
How did you learn about this parameter? What could I do to expose the code
behind the object_id function?
Elbisrever isn'''t.
"markc600@.hotmail.com" wrote:

> You can supply a second parameter to object_id, 'P'
> for stored procedure, 'U' for table etc.
> if object_id('tempdb.dbo.#sp') <> 0
> becomes
> if object_id('tempdb.dbo.#sp','P') <> 0
>|||Tibor,
Thanks for the reply, but you evidently failed to read my post carefully.
Erik
Elbisrever isn'''t.
"Tibor Karaszi" wrote:

> IF OBJECT_ID('tempdb..#tbl') IS NOT NULL ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Erik Eckhardt" <egamlaprov@.liamtoh.moc(reverse each section)> wrote in me
ssage
> news:EFB75273-3DA1-4C04-B6D5-7F3EB9942885@.microsoft.com...
>|||Oh... and you cannot drop the stored procedure until the temp table is
dropped. You must drop them in the order you created them (at least in my
testing).
Oh well, I'm probably worrying over nothing... who's going to be created a
temp table that exactly matches the name of my temp sp? No one...
Elbisrever isn'''t.
"markc600@.hotmail.com" wrote:

> You can supply a second parameter to object_id, 'P'
> for stored procedure, 'U' for table etc.
> if object_id('tempdb.dbo.#sp') <> 0
> becomes
> if object_id('tempdb.dbo.#sp','P') <> 0
>|||> How did you learn about this parameter?
Honestly can't remember, possibly spotted it in a system
stored procedure some time ago.sql