Click here to Skip to main content
12,956,433 members (72,918 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi
Recently i tried to create one temp table before dynamic sql and i compiled the SP.
when i tried to execute the SP it is giving the error like my temp table doen't exist
ex:
PROCEDURE [dbo].[Proc_test]
as
begin
DECLARE @sqlstring NVARCHAR(4000)
select * into #test from xtable
SET @sqlstring='select * from #test'
end

now it is compiling fine but at the time of executing it is showing the error like
#test doesn't exist
Posted 2-Jan-13 2:59am
Updated 2-Jan-13 3:06am
__TR__29K
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

The scope of the temp table is limited to the dynamic query when its defined within the dynamic query. Alternate options are - you can use Global temporary table[^] or create the temp table outside your dynamic query. Take a look at the below link for more information
http://blog.shkedy.com/2007/01/temporary-tables-and-dynamic-sql.html[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,869
CHill60 3,550
Maciej Los 3,302
ppolymorphe 2,080
Jochen Arndt 1,973


Advertise | Privacy | Mobile
Web01 | 2.8.170525.1 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100