"Drop Table" at Stored Procedure

Hello folks,
i like to change a sql-skript to a procedure. The skript includes 20
statements like
select ... into #temp
and
drop table #temp
It runs as a batch if i use
drop table #temp
go
In order to change it to a procedure i change all "drops" to
drop table #temp;
but i can't create the procedure.
The SQL-Server error message was
'Es gibt bereits ein Objekt #Temp'
(translated 'Object #temp still exists')
This Message was shown five times and point to the Select-Staments (not to
all).
To move the drop - Statement direct before the select does'nt help to solve
this behaivor;
I have no idea to solve this problem.
Thanks in advance for any help.
NielsCREATE PROC mysp
AS
IF OBJECT_ID('TempTable') IS NOT NULL
DROP TABLE TempTable
"nieurig" <nieurig@.discussions.microsoft.com> wrote in message
news:045E2CDA-E8BF-4F55-8668-EC1B47139314@.microsoft.com...
> Hello folks,
> i like to change a sql-skript to a procedure. The skript includes 20
> statements like
> select ... into #temp
> and
> drop table #temp
> It runs as a batch if i use
> drop table #temp
> go
> In order to change it to a procedure i change all "drops" to
> drop table #temp;
> but i can't create the procedure.
> The SQL-Server error message was
> 'Es gibt bereits ein Objekt #Temp'
> (translated 'Object #temp still exists')
> This Message was shown five times and point to the Select-Staments (not to
> all).
> To move the drop - Statement direct before the select does'nt help to
solve
> this behaivor;
> I have no idea to solve this problem.
> Thanks in advance for any help.
> Niels
>|||It's a resolution issue. Consider naming the temporary table differently in
each create. The other option is to use dynamic execution, but such approach
involves many issues of its own.
BG, SQL Server MVP
www.SolidQualityLearning.com
"nieurig" <nieurig@.discussions.microsoft.com> wrote in message
news:045E2CDA-E8BF-4F55-8668-EC1B47139314@.microsoft.com...
> Hello folks,
> i like to change a sql-skript to a procedure. The skript includes 20
> statements like
> select ... into #temp
> and
> drop table #temp
> It runs as a batch if i use
> drop table #temp
> go
> In order to change it to a procedure i change all "drops" to
> drop table #temp;
> but i can't create the procedure.
> The SQL-Server error message was
> 'Es gibt bereits ein Objekt #Temp'
> (translated 'Object #temp still exists')
> This Message was shown five times and point to the Select-Staments (not to
> all).
> To move the drop - Statement direct before the select does'nt help to
> solve
> this behaivor;
> I have no idea to solve this problem.
> Thanks in advance for any help.
> Niels
>|||Thanks to Itzik and Uri !!
I will use the workaround with different names of my temp-table.
Have a nice day.
Niels