Problem on ## table

Hi
I have a stored procedure , wherein i create a ## table and get values in
it, i have 3 more procedures which i call from the original procedure which
use the ##table.
When i execute the stored procedure for first time i get the desired
results. when i re-run it again it says the ## table exists . so when i
delete the ## table from tempdb and then re-run it again it works.
Is there a way to delete this ## table right in the begining of execution of
the stored procedure. when i try drop table ## it says the table does not
exists for the first time of execution, if i put the code if (exist whis
table = ##) even this does not work, if i say delte from tempdb.dbo.## even
this does not work
any suggestionTry,
if object_id('tempdb..##t') is not null
drop table ##t
...
AMB
"Rodger" wrote:

> Hi
> I have a stored procedure , wherein i create a ## table and get values in
> it, i have 3 more procedures which i call from the original procedure whic
h
> use the ##table.
> When i execute the stored procedure for first time i get the desired
> results. when i re-run it again it says the ## table exists . so when i
> delete the ## table from tempdb and then re-run it again it works.
> Is there a way to delete this ## table right in the begining of execution
of
> the stored procedure. when i try drop table ## it says the table does not
> exists for the first time of execution, if i put the code if (exist whis
> table = ##) even this does not work, if i say delte from tempdb.dbo.## eve
n
> this does not work
> any suggestion|||It sounds like you should use a local temp table rather than a global
temp table. Specify #tablename rather than ##tablename. Put the DROP
statement at the END of your code that uses the table so that it cleans
up there rather than at the beginning.
David Portas
SQL Server MVP
--|||1) If the stored Procedure is not running multiple instances concurrently,
(i,e., two processes calling it at the same time or overlappiing) Then why
don't you delete it at the end, when the stored Proc is done?
Drop Table ##TableName
or
2) Make it a permanent table and just delete all therecords in it a tteh
beginning (And maybe also at the end) of the stored proc.
or, if the SPs DO run concurrently, you could
3) change the data structure of the temp table so that records being
inserted can be identified as to which instance of the SP they were created
from, (Add an "Instance" or "RunNo" Column) and then modify all other
statements in the SP to affect only those records,
"Rodger" wrote:

> Hi
> I have a stored procedure , wherein i create a ## table and get values in
> it, i have 3 more procedures which i call from the original procedure whic
h
> use the ##table.
> When i execute the stored procedure for first time i get the desired
> results. when i re-run it again it says the ## table exists . so when i
> delete the ## table from tempdb and then re-run it again it works.
> Is there a way to delete this ## table right in the begining of execution
of
> the stored procedure. when i try drop table ## it says the table does not
> exists for the first time of execution, if i put the code if (exist whis
> table = ##) even this does not work, if i say delte from tempdb.dbo.## eve
n
> this does not work
> any suggestion|||You might want to consider writing a single procedure that replaces
this chain of calls. I have found that temp tables can often be
replaced with VIEWs, derived tables or CTEs.
Programming with temp tables in SQL Server is usually a sign that the
programmer is mimicking a procedural file system model of data in which
each of a series of procedural steps is written to a temp table (aka
"Scratch tape") and processed sequentially.