sreecareer (9/6/2010)
Thank you for the quick reply...I think, I need a global temp table because I am using EXEC('query') also in the SP to insert to the temp table. If we use EXEC, I think, it will create a local connection again, and so the local temp table will not be available in that session.
Not entirely true.. Dynamic queries can refer a temp table created in the parent session.. Surprised? Here is a code snippet to check it :
SET NOCOUNT ON
DECLARE @Dyn_SQL VARCHAR(1000)
SET @Dyn_SQL = ''
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
; with cte (N) as
(
select 1 n union all
select 1 n union all
select 1 n union all
select 1 n union all
select 1 n
)
SELECT N into #Temp FROM cte
SELECT @Dyn_SQL = 'SELECT N FROM #Temp ; UPDATE #Temp SET N = N + 1 ; SELECT N FROM #Temp ; DROP TABLE #Temp '
EXEC (@Dyn_SQL)
SELECT N FROM #Temp