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:23 am
[font="Verdana"]try this ...
--Declare @strSql nvarchar(4000)
Begin
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
Instead Dynamic, use Static SQL to create such objects. Can anybody explain in details reason behind it?
Mahesh[/font]
MH-09-AM-8694
April 16, 2008 at 6:24 am
When you execute your create the temp table through dynamic SQL you are creating that temp table in a context that is completely separate from the context of the stored procedure. Therefore, your stored procedure cannot access that table. Create your temp table directly rather than using dynamic SQL.
April 16, 2008 at 6:38 am
April 16, 2008 at 8:09 am
Since the temp table is created by dynamic SQL, it is only accessible within the same dynamic SQL.
You either need to add your inserts/updates/deletes and your final select, to the dynamic SQL, or you need to create the temp table directly in the proc, not in dynamic SQL. Either one will work.
A temp table created in one proc can be accessed by other procs that are called by that proc. Same goes for creating a temp table in an open connection and keeping the connection open to call procs, other batches, etc. Beyond those, temp tables disappear when the batch/proc calling them is done. Dynamic SQL is its own batch, so a temp table created in it is only accessible to things in the same dynamic SQL batch.
- 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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply