Thursday, 7 April 2011

Can I get select results from a Global or local temp table After executing a stored proc?


create proc sp1
                as            select * into ##tem from emp
                  exec sp1
                 --(9 row(s) affected)
                 select * from ##tem
                 --(9 row(s) affected)
                create proc sp2
                as
                select top  2 * into ##tem from emp
                exec sp2
                Error:Msg 2714, Level 16, State 6, Procedure sp2, Line 3
                There is already an object named '##tem' in the database.
  create proc sp4
  as         
  select * into #teR from emp
    exec sp4
   --(9 row(s) affected)
   select * from #teR
  --Msg 208, Level 16, State 0, Line 1
   --Invalid object name '#teR'.
   create proc sp5
   as
   select top  2 * into ##teR from emp
                exec sp5
--(2 row(s) affected)
 select * from #teR
                --Msg 208, Level 16, State 0, Line 1
                --Invalid object name '#teR'.

No comments:

Post a Comment