#temp best practice sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

#temp best practice sp

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-23 : 13:51:52

When building a stored procedure that utilizes temporary tables
what is the best practice and universal syntax as far as the check for it's existence.

Is this it?

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#temp') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tempdb.dbo.#temp

and/or is it necessary?

Cause that is not all there is to naming a #temp table it is also unique along the ___________________________xxxxxx part of it's name and ID

I've seen this around a few times and don't really get the implication.
I know that each #temp is atomic per connection and dropped immediately once it's out of scope.
Just trying to find out what the pros do. Don't want the next person to come through here to say "what a dink"

And it seems when in query analyser this would be a good thing to put at the top of the batch as to avoid
cyclical highlighting of the Drop Table command.

Here's some interesting stuff

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#temp') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.#temp
works better than the first one because the first throws non-critical message about tempbd being ignored on the DROP TABLE part.

BOL claims object_id must contain temdb. qualifier.

Probably all this is a none issue

From some searches

quote:

Why not try this?

if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID(N'tempdb..#tmpTable'))
drop table #tmpTable
I use this all the time and it works!!!

Edited by - Sitka on 08/23/2002 14:29:59

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-23 : 14:58:13

Basically, I don't think you need to check for the temporary table's existence at the beginning, because each new connection will create it's own temp table as per the ____XXX name part you mention. So all you should need to do is create the table at the beginning, and it'd be nice if you'd drop it when you're done with it, even though SQL will drop it on its own. It's kind of like closing and destroying objects in ASP/VB. The code will still run if you don't, but it's better practice to clean up after yourself.

Also remember that if you're on SQL 2000, you can use table variables in place of temp tables.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-23 : 16:06:52

Thanks

quote:
you can use table variables in place of temp tables.

Here I am in my pursuit of relational thought and stuck on SQL7

Actually, pondering the table variables generated this question.
Figured better try to do things as well as I can.

Voted best SQL forum nickname...."Tutorial-D"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-24 : 18:03:33

If you think you need to check for it's existance before you create a temp table there is probably something wrong with the design.
People often think this is necessary because they test the code from an SP in a query window and find that after the first test get a create temp table error but don't realise that this will never happen with the SP.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-24 : 22:12:35

quote:
I've seen this around a few times and don't really get the implication. I know that each #temp is atomic per connection and dropped immediately once it's out of scope. Just trying to find out what the pros do.

Thanks nr

I wanted to put this to rest...but....what if the sp is called twice by the same connection (can it be?) and the table wasn't dropped at the end of the first? Anyways no big deal, lots of best practice advice out there as far as calling DROP at the end.

Voted best SQL forum nickname...."Tutorial-D"

Edited by - Sitka on 08/24/2002 22:15:06

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-25 : 13:55:09

If you call an SP which creates a temp table then that table will be dropped (automatically) at the end of the SP. If you call the SP again then it will create a new one happily.

You may get problems if the SP calls itself - directly or indirectly but if you are trying this you should know what you're doing or you deserve the problems.

Depending on the version of sql server you may get errors (or strange results) if an sp with a temp table calls another one which creates one with the same name - so keep names unique.
Those like me who appreciate the brevity of #a, #b, ... will know all about this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources