using multiple temp tables - 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

SQL Server Development (2000)

using multiple temp tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-18 : 07:26:29

Jason writes "Hi,

I'm trying to create a stored procedure, maybe multiple stored procedures that use more than one temp table.

Example

I open a procedure with 6 variables passed from an asp page and immediately create a temp table.
Then I want to create another temp table that is created from combined fields of the previous temp table and a static table already in the db.

Then I want to do the same a third time using temp table 2 and 2 other static tables.

The final output is a recordset that is a select statement from the final (3rd) temp table with the 6 variables from the beginning as the dynamic where clause.

Here is where I am at so far:
A procedure (w/o variables so far) that creates a temp table then the 2nd temp is built using temp 1 and a static.

The error says that that temp 2 is already in database?

Any suggestions?

Set ANSI_WARNINGS ON
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE open_sales_order AS

--make F4211
CREATE TABLE #sql_F4211 (OrderCompany varchar (5), OrderNumber numeric (8), OrderType varchar (2), LineNumber numeric (6), BP varchar (12), Company varchar (5), BillTo numeric (8), ShipTo numeric (8), RequestDate datetime, OrderDate datetime, ShipDate datetime, ShortItemNumber numeric (8), LegacyNumber varchar(25), NextStatus varchar (3), LastStatus varchar (3), OrderQuantity numeric (15), ExtendedPrice numeric (15) )
Insert Into #sql_F4211 Select * From OPENQUERY(JDE_LINK, "SELECT SDKCOO as OrderCompany, SDDOCO as OrderNumber, SDDCTO as OrderType, SDLNID as LineNumber, SDMCU as BP, SDCO as Company, SDAN8 as BillTo, SDSHAN as ShipTo, SDDRQJ as RequestDate, SDTRDJ as OrderDate, SDADDJ as ShipDate, SDITM as ShortItemNumber, SDAITM as LegacyNumber, SDNXTR as NextStatus, SDLTTR as LastStatus, SDUORG as OrderQuantity, SDAEXP as ExtendedPrice FROM F4211 WHERE SDCO='00005'")
--Select OrderCompany, OrderNumber, OrderType, LineNumber, BP, Company, BillTo, ShipTo, RequestDate, OrderDate, ShipDate, ShortItemNumber, LegacyNumber, NextStatus, LastStatus, OrderQuantity, ExtendedPrice FROM #sql_F4211
GO

--make SOt
Create TABLE #sql_SOT (BP varchar (12), OrderNumber numeric (8), ShortItemNumber numeric (8), OrderDate datetime, OrderQTY numeric (15), ExtPrice numeric (15), LegacyNum varchar (25), Description1 varchar(30), Description2 varchar(30), GLCatCode varchar (4))
Insert Into #sql_SOT
SELECT CAST([BP] as numeric), #sql_F4211.OrderNumber, #sql_F4211.ShortItemNumber, #sql_F4211.OrderDate, Sum(#sql_F4211.OrderQuantity) AS OrderQTY, Sum(#sql_F4211.ExtendedPrice) AS ExtPrice, sql_F4101t.LegacyNum, sql_F4101t.Description1, sql_F4101t.Description2, sql_F4101t.GLCatCode
FROM #sql_F4211 LEFT JOIN sql_F4101t ON #sql_F4211.ShortItemNumber = sql_F4101t.ShortItemNumber
WHERE ((#sql_F4211.OrderType) Not In ("ST","S2","SQ"))
GROUP BY BP, #sql_F4211.OrderNumber, #sql_F4211.ShortItemNumber, #sql_F4211.OrderDate, sql_F4101t.LegacyNum, sql_F4101t.Description1, sql_F4101t.Description2, sql_F4101t.GLCatCode
GO

Drop Table #sql_F4211
GO
Drop Table #sql_SOT
GO

SET QUOTED_IDENTIFIER ON
GO"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-18 : 07:40:22

If the temp table already exists then you need to make sure that it is droped before being created again.

Something like :


IF (OBJECT_ID('TEMPDB..#tempTable')) > 0
DROP TABLE #tempTable
GO

This will drop the table if it exists. Place this before the CREATE statment.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-08-18 : 08:53:39

HI,

I think, when we create any local temp(#) table in stored procedure, it will drop automatically.When we create global temp(##)table, we have drop externally.Am i right? How this error comes?

Send me reply ASAP.

":-) IT Knowledge is power :-)"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-18 : 09:15:20

As you said, # or local temporary tables are only visible in the current session while ## global temporary tables are visible across all other sessions.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources