temp tables in sproc problem

[jepatte]

I need to create a temp table in a sproc but I an getting a strange error.

---------------------------------------
CREATE PROCEDURE [dbo].[sp_UpdateTotalsAgePrecinct]
(
@fromAge as Integer, @toAge as Integer
)
AS

IF @toAge > 0
SELECT NCOACounty, NCOAPrecinct INTO #tmpPrecinctAge
FROM dbo.AllData
ELSE
SELECT NCOACounty, NCOACountyCode INTO #tmpPrecinctAge
FROM dbo.AllData
---------------------------------------

I get the error #tmpPrecinctAge is already an object. The error is on the line that starts after the else. If I take out the Else and last sql statement, I can save it fine, but as is above, I can save this.

 

[bborissov]

Did you have DROP TABLE #tmpPrecinctAge at the end of SP?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JackVam]

I would do an explicit CREATE..DROP , to manage it properly

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[jepatte]

Sorry, yes, I had a drop at the end. I still get "#tmpPrecinctAge is already an object". I get this message when I create the sproc though.

 

[bborissov]

Try

CREATE PROCEDURE [dbo].[sp_UpdateTotalsAgePrecinct]
(
    @fromAge as Integer, @toAge as Integer
)
AS
IF OBJECT_ID('TempDB..#tmpPrecinctAge') IS NOT NULL
   DROP TABLE #tmpPrecinctAge

IF @toAge > 0
    SELECT NCOACounty, NCOAPrecinct INTO #tmpPrecinctAge
    FROM dbo.AllData
ELSE
    SELECT NCOACounty, NCOACountyCode INTO #tmpPrecinctAge
    FROM dbo.AllData
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

that won't work...you will get the same error because of deferred name resolution

first create the table before the if..else junk
then insert into the table, common n00b error



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jepatte]

I just read an article that the same temp table can't be created in a sproc even if its in an if-else. I assume this is a limitation to at least SQL 2000 and before the sproc is compiled, there must be something in memory disallowing this.

 

[SQLDenis]

i told you already that you can't do it

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

Also the SELECT INTO syntax that you are using should pretty much always be avoided.
See the first part of:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1284087,00.html

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog