>>>
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.
If it's not broken, fix it until it is.
My Blog