A problem about creating temp table

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)

A problem about creating temp table

Author

Topic

zhongronnie
Starting Member

2 Posts

Posted - 2003-07-03 : 03:44:01

procedure1:
CREATE PROCEDURE TEST1
AS
CREATE TABLE #Temp1 (COL1 VARCHAR(50))
SELECT * FROM #Temp1
GO

procedure2:
CREATE PROCEDURE TEST2
AS
DECLARE @SQL VARCHAR(8000)
SET @SQL =
'CREATE TABLE #Temp2 (COL1 VARCHAR(50))'
EXEC (@SQL)
SELECT * FROM #Temp2
GO

Why procedure1 can work,but procedure2 not!
Thanks!Please help me.

GOOD LUCK

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-03 : 03:47:18

In procedure2, the temp table is created in another context because of dynamic SQL. Therefore, you cannot reach it from outside the dynamic SQL statement. You could use a global temp table if you are not worried about concurrency.

zhongronnie
Starting Member

2 Posts

Posted - 2003-07-04 : 09:49:34

Thanks!

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources