June 13, 2009 at 1:20 am
Hi,
I could not view the execution plan for some of the stored procedures that have
select * into from tablename.
Also I could not find them in the DMVs.
Is it a bad practice to have select * into from tablename in the stored procedure?
Regards,
Prakash.
R
June 13, 2009 at 4:40 am
What do you mean by 'cannot view execution plan'? If you run the proc in management studio with the "Include Actual Execution Plan" option selected, what happens?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2009 at 5:33 am
Hi,
It is production environment, so I can not execute the stored procedure.
I can not view the estimated execution plan for the SP.
Msg 208, Level 16, State 0, Procedure usp_GetData, Line 217
Invalid object name '#TableName'.
Line no 210 :select * into #tablename from Tbl
Line no 217 : select * from #tablename
Regards,
Prakash.
R
June 13, 2009 at 5:36 am
You can never view the estimated exec plan for a proc that creates and uses a temp table. The temp table doesn't exist at compile time, so you get the error.
Try creating the temp table first, or run the proc on a dev/test server and look at the actual exec plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply