Problem with Temp Tables

[MarvinJean]

I am trying to create a stored procedure and can not get temp tables created in the proc to pass to subsequent steps of the same proc.

The first step SELECTs INTO a temp table #tblCertRollTest

The next step uses that table to SELECT INTO another temp table #tblTest

#tblTest is used to SELECT INTO the next temp table #tblTested and so on.

The proc runs properly when regular tables are used eg the tables above are named rtblCertRollTest, rtblTest, rtblTested. I need the tables to be user unique.

When the rtbl prefix is changed to #tbl It gives an error msg Server: Msg 208, Level 16, State 1, Procedure procTestedRollsTmp, Line 73
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#tblTest'.

The first #tblCertRollTest was not available to be pulled into the second SELECT INTO


The proc that works is listed below - I changed it to temp tables by copying it to notepad and replacing rtbl with #tbl.

This has me stopped for days - If you can help I'd appreciate it

Marvin

*****************************************************

CREATE PROCEDURE [dbo].[procTestedRolls]
@t_Table varchar(40), @t_TableAbr varchar(3), @r_Code int = null
AS

Declare @tstTable Varchar(40)
Declare @tstTableAbr varchar(3)
Declare @SQL1 Nvarchar(4000)
Declare @SQL2 Nvarchar(4000)
Declare @SQL3 Nvarchar(4000)
Declare @tstcertroll varchar(16)

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'rtblCertRollTest' )
Drop Table rtblCertRollTest
SELECT IDENTITY(smallint, 1, 1) AS Testid, tblSapRollPos.SapLotNumber,
tblSapRollPos.SapRoll, tblPly.Ply INTO rtblCertRollTest
FROM tblPly, (rviewJobCert INNER JOIN tblSapRollPos ON
rviewJobCert.StretchLot = tblSapRollPos.SapLotNumber)
INNER JOIN tblSapLotHdr ON tblSapRollPos.SapLotNumber =
tblSapLotHdr.SapLotNumber
GROUP BY tblSapRollPos.SapLotNumber, tblSapRollPos.SapRoll, tblPly.Ply,
tblSapLotHdr.StretchPlies
HAVING (((tblPly.Ply)<=[StretchPlies]))
ORDER BY tblSapRollPos.SapLotNumber, tblSapRollPos.SapRoll, tblPly.Ply;

CREATE UNIQUE CLUSTERED INDEX CertRollTest_Ind ON rtblCertRollTest (TestId)

PRINT 'rtblCertRollTest All Rolls in tblSapRollPos eg all rolls/plies FOR ' + @tstTable
select * from rtblCertRollTest

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'rtblTest' )
Drop Table rtblTest
Set @tstTable = @t_Table
Set @tstTableAbr = @t_TableAbr
SET @SQL1 =
'SELECT IDENTITY(int, 1, 1) AS Testid, rtblCertRollTest.SapLotNumber,
rtblCertRollTest.SapRoll, rtblCertRollTest.Ply, '
+ @tstTable + '.SapLotNumber AS tstSapLotNumber, '
+ @tstTable + '.SapRoll AS tstSapRoll, '
+ @tstTable + '.SapPly AS tstSapPly INTO rtblTest '
+ ' FROM rtblCertRollTest LEFT JOIN ' + @tstTable + ' ON
(rtblCertRollTest.Ply = ' + @tstTable + '.SapPly)
AND (rtblCertRollTest.SapRoll = ' + @tstTable + '.SapRoll)
AND (rtblCertRollTest.SapLotNumber = ' + @tstTable + '.SapLotNumber)
Order By rtblCertRollTest.SapLotNumber, rtblCertRollTest.SapRoll,
rtblCertRollTest.Ply'
-- PRINT @SQL1
EXECUTE sp_executesql @Sql1

CREATE UNIQUE CLUSTERED INDEX RollTest_Ind ON rtblTest (TestId)

PRINT 'rtblTest from rtblCertTestRoll and tblSapTest eg all tested rolls/plies FOR ' + @tstTable
select * from rtblTest


IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'rtblTested' )
Drop Table rtblTested
SET @Sql2 =
'SELECT IDENTITY(int, 1, 1) AS Testid, ' + @tstTable + '.SapLotNumber AS tstSapLotNumber, '
+ @tstTable + '.SapRoll AS tstSapRoll, '
+ @tstTable + '.SapPly AS tstSapPly INTO rtblTested
FROM ' + @tstTable + ' INNER JOIN viewJobCert ON ' + @tstTable + '.SapLotNumber = viewJobCert.StretchLot '
+ ' Where (NOT ( ' + @tstTable + '.SapRoll IS NULL))
Order By tstSapLotNumber, tstSapRoll, tstSapPly'
-- Print @Sql2
EXECUTE sp_executesql @Sql2

CREATE UNIQUE CLUSTERED INDEX RollTested_Ind ON rtblTested (TestId)

Print 'rtblTested from tstSapRoll eg only those tested rolls - per roll-rules FOR ' + @tstTable
Select * From rtblTested

IF (Select Count(*) From rtblTested Where rtblTested.tstSapRoll is not null) = 0
Begin
PRINT 'NO Records For ' + @tstTable
Return
end
Else
PRINT 'Tested Roll Exist...Continue ' + @tstTable

UPDATE rtblTest
SET tstSapRoll = td.tstSapRoll
FROM rtblTest t, rtblTested td
Where t.SapLotNumber = td.tstSapLotNumber
and t.SapRoll = td.tstSapRoll
Print 'rtblTest from rtblTest join rtblTested ON SapLotNumber,SapRoll Pulg in Rolls Tested...Not Tested to NULL ' + @tstTable
select * From rtblTest

while exists (select * from rtblTest where tstSapRoll is null)
update rtblTest
set tstSapRoll = t2.tstSaproll -- , tstSapPly = t2.tstSapPly
from rtblTest
join rtblTest t2
on t2.TestId = rtblTest.TestId - 1
and rtblTest.tstSapRoll is null
and t2.tstSapRoll is not null

Print 'rtblTest from rtblTest with all coa rolls with tested rolls FOR ' + @tstTable
select * From rtblTest

Set @tstcertroll = @tstTableAbr + 'certroll'
Set @Sql3 =
'UPDATE tblCert
SET tblCert.' + @tstcertroll + '= rtblTest.tstSapRoll
FROM tblCert JOIN rtblTest
ON rtblTest.SapRoll = tblCert.StretchRoll'
EXECUTE sp_executesql @Sql3
Print 'tblCert with tested rolls FOR ' + @tstTable
Select * from tblCert
GO

 

[johnherman]

Temp table with only one # in front are dropped when that process ends. Use two # in front ##table_name.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[Pattycake245]

The ## syntax is ok as long as there isn't anyone else which runs this procedure. If there is you may get problems since two users will be trying to create the same table. With local temp tables (#) they are stored in tempdb and have an integer appended to the end of the table name for uniqueness. Otherwise, I suppose you could make the global tables unique by appending a time stamp to them.

Also, I've found that creating the table first then populating it performs better than creating the table on the fly. So, instead of:

SELECT IDENTITY(smallint, 1, 1) AS Testid, tblSapRollPos.SapLotNumber,
tblSapRollPos.SapRoll, tblPly.Ply INTO rtblCertRollTest

try:

CREATE TABLE rtblCertRollTest(testid int identity, SapLotNumber int, SapRoll int, Ply int)
insert INTO rtblCertRollTest......

I'm guessing at the data types of course

 

[johnherman]

You can also append the user name to the end of the ##temp table to provide the uniqueness which PattyCake mentioned as a possible problem.

Sometimes the grass is greener on the other side because there is more manure there - original.