Temp Table error

Question

Post reply

Temp Table error

manikandan-1115962

Old Hand

Points: 300
More actions
June 26, 2009 at 7:02 am

#133394

I am writing a Stored procedure to drop a Column name using temperory table. But am getting following error for following sql statement,,

SET @tsql='ALTER TABLE #temp3 DROP COLUMN ['+@pcrColumnName+']'

EXEC(@tsql)

ALTER TABLE DROP COLUMN failed because column 'Car_ID' does not exist in table '#temp3______________________________________________________________________________________________________________0000000000F0'.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

Paul White

SSC Guru

Points: 150457

June 26, 2009 at 7:06 am

#1015990

#temp3 is a local temporary table - it is private to the session that created it.

Dynamic SQL runs in a separate context, so it cannot see the private table. 🙂

edit:

You could try using a global temporary table (## prefix)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Can you post the full code including where the temp table is created please?

Are you sure that the column 'Car_ID' exists in that table because the error indicates that it doesn't

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Paul White (6/26/2009)

#temp3 is a local temporary table - it is private to the session that created it.

Dynamic SQL runs in a separate context, so it cannot see the private table. 🙂

Sure you're not thinking about table variables? Temp tables are visible in the procedure that created them and all procs/dynamic SQL called from there. They're only dropped when the proc that created them ends.

CREATE TABLE #Test (ID INT)



Insert into #Test (ID) Values (1)

Insert into #Test (ID) Values (2)

DECLARE @ColName varchar(10)

SET @ColName = 'col2'

EXEC ('ALTER TABLE #Test ADD ' + @ColName + ' DATETIME DEFAULT GETDATE()')

Insert into #Test (ID) Values (3)

SELECT * FROM #Test

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150457 More actions · Answer 4

GilaMonster (6/26/2009)

Sure you're not thinking about table variables? Temp tables are visible in the procedure that created them and all procs/dynamic SQL called from there. They're only dropped when the proc that created them ends.

No - in fact it turns out I just wasn't thinking at all! 😀

I clearly need to do more dynamic SQL with temp tables lol.

Thanks for the correction.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi