>>>>
If you created a temp table in SP, you can not access this temp table outside the SP. >>>>
>>>>That's not entirely correct. If I start a transaction from .NET, and that transaction runs stored procedure A (which creates temp table ABC), and then runs stored procedure B, stored procedure B will see the temp table.
>>>>
>>>>Temp table scope is based on connection, not a single stored procedure
>>>
>>>I've run this test:
>>>
>>>
>>>use AllTests
>>>go
>>>create procedure FirstProc
>>> as
>>>create table #Test (id int identity, String varchar(10))
>>>insert into #Test
>>>values ('test1'),('test2')
>>>go
>>>
>>>create procedure SecondProc
>>> as
>>>select * from #Test
>>>go
>>>
>>>execute FirstProc
>>>execute SecondProc
>>>
>>>The second procedure was not able to see #Test table.
>>>
>>>If I would change my test and call second SP from the first SP, then it will see the temp table. But you were trying to discuss the first scenario, right?
>>
>>The go statement clears all temp variables... you can do it without the go..
>
>That's not true. GO just separates batches in SSMS. The creation of stored procedure must be the only thing in the batch.
>
>In other words, GO in the code above has nothing to do with the temp tables.
DECLARE @temp INT
SET @temp = 1
GO
print @temp
yields:
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@temp".
ergo my previous assertion of clear local variables is true...