April 16, 2008 at 5:46 am
How to create temp table in Stored procedure in SqlServer 2005.
When I create the Sp it is successfull.
But When I execute it invalid object #Temp is thrown.
What is the problem with the code.
My code is --
Alter PROCEDURE SP_PRJ_Stakeholder
(
@AssignmentId int
)
As
Declare @strSql nvarchar(4000)
Begin
set @strSql = N'Create table #TempTab (SNO int, Activity nvarchar(100), [Client] nvarchar(1000), [Delivery Head] nvarchar(1000), [PDC] nvarchar(1000), [Project Manager] nvarchar(1000), [Team Member] nvarchar(1000), [Tech Supp] nvarchar(1000))'
print @strSql
exec sp_executesql @strSql
SELECT * FROM #TempTab
return
End
April 16, 2008 at 6:30 am
The problem is, executing a dynamic query like that is creating a different execution context. As soon as that context clears, which happens when the execution finishes, the temporary table is dropped.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2008 at 1:59 pm
This is a duplicate post. There are answers on the other copy with details.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply