I revised some of the sp with temp table to eliminate the dozends of declare @vars in each sps. Runing individually, they seem to work great, until I wrote a test case script, looping over the live records in production system to test these unreleased new sps (ATM, I can't think of better ways to loop through parameter sets to test them without a cursor. Please try not to focus on use of cursor here). I got different result during repeated exec.
e.g.
-- sub_sp2:
-- sub_task2
select top 1 col1, col2, .... col30
into #temp_sub_sp2
from tb1
left join tbl_ext1
left join tbl_ext2
left join tbl_ext3
left join tbl_ext....
where cond1 = true
if @@row_count < 0
print cond1=true do A thing
else
print cond1=false do B thing
-- sp1:
-- this is a master sp with a couple of sub tasks written in sub_sps.
exec sub_sp2 param1,...., @task2_result output
exec sub_sp3 param1,...., @task3_result output
exec sub_sp4 param1,...., @task4_result output
-- do sth with @result1
-- test script:
-- a test script to call sp1 repeatedly with
-- different parameters from production system to check result.
declare cur1 ...
for select colx from prd_mach_status
open cur1
fetch next from cur1 into @param1
...
while ...
exec sp1 @param1
The result is like
| param1 | print |
| value1 | cond1 = 1 do A |
| value1 | cond1 = 0 do B |
| value2 | cond1 = 0 do B |
| value1 | cond1 = 0 do B |
The live data varies, but always follow the same pattern. After the 1st cond1 is true, the following exec will all result in cond1 = 0. Even if cond1 is true.
Clearly the select into failed, after the first successful select into. and the temp table is not freed after SP2, or SP1 has terminated.
Here the local #temp_sp2 is created inside sp2's scope, not from outer scope, once the call returns, the creation scope is no longer valid, but the table is not freed. It feels like the latter call to the same sp shares the same scope with the previous calls.
I already know the following :
- Temp tables are just aliased tables with fancy names in tempdb.
- The concept of scopes defines their visibility
- Sessions are top level scopes seperated per connection.
My question is about their lifespan. Usually once an object is created its bound to a local function scope, once the scope is gone, the object is GCed. The local temp table created in a nested sp here clearly didn't follow this.
What is the catch here, how should it be used, when the regular understanding of scope doesn't apply here (e.g. python).
The answer from Scope of temporary tables in SQL Server actually contradicts with this.
Also, there's no need to DROP TABLE at the end of your procedure (from same link again):
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished
From @Panagiotis-Kanavos 's comment, I seem to get the idea that temp table (to be exact their fancy alias mapping, not necessarily the real table) has a scope for visibility/creation but is not auto destroied until the session ends. This explains things, but controdicts with previous statements from suggested question's answer.
Scope and Lifetime don't really apply. SQL isn't C#. #somename is a real table that's created when a create table statement is executed and exists as long as the session/connection is open.
Now I am more confused...
