[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Temp tables inside a stored procedure

MarkusJ_NZ

3/26/2007 11:49:00 PM

Hi, I have some temp tables inside a stored procedure. Is it good
practice to explicitly drop these temp tables at the end of the stored
procedure or will MSSQL drop them for me?

Obviously, best practice would not to have temp tables in the first
place but I am unable to avoid this scenario.

Cheers
Markus
4 Answers

--CELKO--

3/27/2007 1:32:00 AM

0

>> Obviously, best practice would not to have temp tables in the first place but I am unable to avoid this scenario. <<

I agree! We can try to fix for you. Your real problem is that you
already have decided you are defeated and are asking for the best way
to lose.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

It is very hard to debug code when you do not let us see it.

xyb

3/27/2007 1:38:00 AM

0

On 3?27?, ??7?49?, Markus...@gmail.com wrote:
> Hi, I have some temp tables inside a stored procedure. Is it good
> practice to explicitly drop these temp tables at the end of the stored
> procedure or will MSSQL drop them for me?
>
> Obviously, best practice would not to have temp tables in the first
> place but I am unable to avoid this scenario.
>
> Cheers
> Markus

Two types of temp table you may have created,first is with prefix ##
as global temp table whitch can be hold untill you use drop table
clause and can be seen from different connection handle, second is
with prefix # as transaction temp table or connection temp table
whitch can only be seen at current connection.
Although the temp table you create inside a proc with either prefix #
or perfix ## will drop automatictly.

TheSQLGuru

3/27/2007 2:42:00 AM

0

You might be surprised what the very sharp people on this forum can do to
avoid temp tables, remove cursors, etc. I agree with Celko - show us some
code/schema!

To directly answer your point, yes, you should explicity drop all temp
tables.

Also, can you use table variables instead? They can have much better
performance in some cases.

--
TheSQLGuru
President
Indicium Resources, Inc.

<MarkusJNZ@gmail.com> wrote in message
news:1174952959.904919.115730@b75g2000hsg.googlegroups.com...
> Hi, I have some temp tables inside a stored procedure. Is it good
> practice to explicitly drop these temp tables at the end of the stored
> procedure or will MSSQL drop them for me?
>
> Obviously, best practice would not to have temp tables in the first
> place but I am unable to avoid this scenario.
>
> Cheers
> Markus
>


MarkusJ_NZ

3/27/2007 3:56:00 AM

0

On Mar 27, 12:42 pm, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
> You might be surprised what the very sharp people on this forum can do to
> avoid temp tables, remove cursors, etc. I agree with Celko - show us some
> code/schema!
>
> To directly answer your point, yes, you should explicity drop all temp
> tables.
>
> Also, can you use table variables instead? They can have much better
> performance in some cases.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> <Markus...@gmail.com> wrote in message
>
> news:1174952959.904919.115730@b75g2000hsg.googlegroups.com...
>
>
>
> > Hi, I have some temp tables inside a stored procedure. Is it good
> > practice to explicitly drop these temp tables at the end of the stored
> > procedure or will MSSQL drop them for me?
>
> > Obviously, best practice would not to have temp tables in the first
> > place but I am unable to avoid this scenario.
>
> > Cheers
> > Markus- Hide quoted text -
>
> - Show quoted text -

Hi all, thanks for your help everyone. They are just #temp tables,
I'll see what I can come up with in regards to MSSQL and post it here.

Thanks
Markus