CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresreporting-servicessql-server-2012
Moshin Khan
Moshin Khan

Reputation: 185

tsql sql multiple temp tables with same name

I'm working on a Report using RDL files and stored procedures. RDL file name is test.rdl and I have four(4) stored procedures connected to this test.rdl file. so this file will have four datasets.

report file name:

test.rdl

stored procedure names:

each of these stored procedures have temp tables with same name(#temp).

create table #temp(
ID int,
Name varchar(max),
Location varchar(max)
)

insert into #temp

select * from anothertable where date between '20171001' and '20171031'

some more extra queries to get the desired result and then finally drop the temp table.

select . . . . . . . . . .

drop table #temp

so all of the four stored procedures have temp table using one name(#temp).

so my question is, will this work fine when the RDL test file is ran and when all of the four(4) stored procedures are called at once? my concern is with the #temp table, all four stored procedures have drop table #temp at the end of the run. will this affect the filling of the temp tables during runtime as one of the stored procedures may finish with drop table while the other one was running hence resulting in cancelling of query or finishing with incomplete results?

can some please explain. I hope this is a valid question.

Upvotes: 2

Views: 3269

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Broadly speaking.... Local temp tables (using a single #) are only available to the instance of the procedure that called them or procedures that were called from the same procedure as the one that created the temp table.

If you call a procedure 10 times simultaneously, each has it's own "copy" of the temp table.

From your description it sounds like you just have 4 datasets, each running a Stored Procedure and each of these SP's creates a temp table called #temp. You don't have an SP that calls the other SP's...

Assuming that's correct then it will work fine, in fact you don't even have to drop the temp table at the end of the proc as this done automatically.

The fact that the temp tables are all referenced using the same name doesn't matter, the actual name of the physical temp table stored in the database will not be #temp but something more like tempdb.dbo.#temp_______________000000000123, the point is that they are in fact all unique tables in the database but SQL Server handles the translation to/from the name you created it under automatically.

Upvotes: 10

Related Questions