temporary table - 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)

temporary table

Author

Topic

yaronkl
Starting Member

1 Post

Posted - 2004-02-01 : 12:44:16

Hi

I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database

This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.

declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end

IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end

Than you.

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-01 : 15:07:07

Not sure, but try to bracket the code with a Stored Procedure ??

CREATE PROCEDURE dbo.MyProc

AS
declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end

IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end
RETURN
GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-01 : 20:11:50

You cannot declare the same local temp table more than once per execution block.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources