1

I would like a bit better understanding of the #Temptable scope across stored procedures within one session. My understanding is that #TempTable has local scope to the current session where it has been created. What I am after is to create a temp table in a store procedure 1, then fill it in another sp2 which I will call from within sp 1. This way I will have full table to work with in sp1(data filled in sp2). Now it seems to work fine, however I am not sure if there are any hidden problems which I may see. Any one have any idea? The reason I am using this approach is that I can not use insert exec nested statements (sp2 already calls insert into exec). Thanks in advance Following is sample of my code logic for clarification of question

create table #TempToBeFilledInAnotherSp(
    col1 int,
    col2 int
);
exec spe2 param1, param2;--this sp will insert data in #TempToBeFilledInAnotherSp

--Now that I have all the data in temp table which I created here I can use it
select * from #TempToBeFilledInAnotherSp;--or do my further processing on the data
user1063108
  • 662
  • 1
  • 10
  • 24
  • You might be able to use a global ##TempTable. – Shawn Mar 01 '18 at 19:41
  • True, but if when a user calls sp1 at the same time if another user calls sp1 then there is concurrency data problem. I am using local so that all data is local to each call..Just wondering if this is terrible idea though it seems to work – user1063108 Mar 01 '18 at 19:43
  • The global temp table will automagically drop itself when the opening session closes and when all other active sessions close. Since you are calling sproc2 from sproc1, I think it should still be active when sproc2 tries to access it. – Shawn Mar 01 '18 at 19:43
  • Wouldn't it be cleaner to pass around a table parameter? – jleach Mar 01 '18 at 19:44
  • True but apparently I can not pass a table parameter to SLQ CLR stored procedure – user1063108 Mar 01 '18 at 19:45
  • and sp2 which is called from withing main sp1 is a CLR stored procedure – user1063108 Mar 01 '18 at 19:46
  • https://stackoverflow.com/questions/34923027/ms-sql-server-safe-concurrent-use-of-global-temp-table – Shawn Mar 01 '18 at 19:47
  • 2
    If you create a temp table in Procedure1, that temp table is available for any other procedures that Procedure would call. The scope is within the execution. When that procedure completes the temp table will be dropped. This is actually a good approach to this type of thing, albeit a little bit brittle in that procedure2 will no longer work if the temp table isn't created already. – Sean Lange Mar 01 '18 at 19:53
  • Thanks, Perfect. Because the life the temp table must seize with the call completing in SP1. So sp2 will never be called from anywhere but from sp1 which would always create temp table – user1063108 Mar 01 '18 at 19:59

2 Answers2

0

If a #temp table is created outside of a stored procedure, then it is scoped to the Session and is visible to any execution context within that session.

However, if a #temp table is created within a stored procedure then it is only scoped to that execution context and is only visible to that execution context and to any execution contexts that it creates (other stored procedures that it calls).

In short, if you create a #temp table within a stored procedure, then it will be deleted whenever that stored procedure exits.

If you want to create a #temp table in one stored procedure and have it be visible in another stored procedure, the only way to do that is to have the first stored procedure call the second stored procedure (or call a stored procedure that calls another, that calls another, ... etc..., that calls the stored procedure you want to use the #temp table).

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
-1

#Temp tables are local to the connection that created them. Other connections can't access them, and when the connection is closed, they are dropped.

All code that executes in the same connection will have access to temp tables created within that connection, even if the code occurs in different procedure calls, different transactions, etc. This will hold true until the connection is closed or the temp table is explicitly dropped.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • ok, so are you suggesting that if I were to use the above logic in a web application, which mostlikely will share a single connection to run multiple calls to sp. In that case if a user calls sp1, and at same time another user calls sp1 then both users will have access to temp table which was created by first users call to sp1? – user1063108 Mar 01 '18 at 21:34
  • Depends on how the application is written. If it creates a new database connection/session for each app-user, then no. If two app-users literally share a database connection/session, then yes both users will have access to the same temp table. – Tab Alleman Mar 02 '18 at 14:23