Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Temp table is SP

0 views
Skip to first unread message

Michael Tissington

unread,
Mar 13, 2003, 10:53:59 PM3/13/03
to
I'm using a temp table inside an SP that I have created.

My understanding is that it remains for the life of the connection so if I
execute the sp again from the same connection the temp table will still
exist.

In SQL 2000 I resolve this by putting a DROP TABLE inside my SP.

However it seems that on SQL 7 this its not valid to have a DROP TABLE
inside in SP.

Is there a way to accommodate both SQL 2000 and SQL 7 ?

Thanks.

--
Michael Tissington
Oaklodge Technologies
http://www.oaklodge.com/technology

Kalen Delaney

unread,
Mar 13, 2003, 11:40:53 PM3/13/03
to
If you create a temp table INSIDE a stored procedure, it will be dropped
when the stored procedure exits. You should not need a DROP TABLE in either
SQL 7 or 2000.

A temp table created OUTSIDE a stored procedure will exist for the life of
the connection, or until it is dropped.

HTH

--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Michael Tissington" <mic...@nospam.com> wrote in message
news:#tYPj1d6...@TK2MSFTNGP12.phx.gbl...

Vinodk

unread,
Mar 14, 2003, 2:40:43 AM3/14/03
to
Hi,
I agree partly to what Kalen has to say ... See the temporary tables do
get cleared from memory after the connection gets closed. But even when the
connection persists its always a BETTER PRACTICE to drop the temporary
tables as soon as it has serverd its purpose.
Hoding a resource is costly with respect to server ... And it can be
better utilized by other processes ... The effect can be realized in
multiuser scenarios (iam talking in the range of couple of thousands) ...

--
Thanks,
Vinod Kumar
MCSE, DBA, MCAD


"Kalen Delaney"
<kalen_please_reply_on_p...@compuserve.com> wrote in
message news:engTmPe6...@TK2MSFTNGP12.phx.gbl...

Michael Tissington

unread,
Mar 14, 2003, 7:08:30 PM3/14/03
to
Can I use a drop table in a trigger in SQL 7 ?
If not what is the scope of the temp table?

--
Michael Tissington
Oaklodge Technologies
http://www.oaklodge.com/technology

"Kalen Delaney"
<kalen_please_reply_on_p...@compuserve.com> wrote in
message news:engTmPe6...@TK2MSFTNGP12.phx.gbl...

Tibor Karaszi

unread,
Mar 17, 2003, 2:21:35 AM3/17/03
to
A trigger behaves in the same way as a stored procedure regarding scope of temp table.

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


"Michael Tissington" <mic...@nospam.com> wrote in message

news:OixvNco6...@TK2MSFTNGP11.phx.gbl...

0 new messages