Temp Table usuage in a Multi User Environment

Question

here's the situation:

I have an SSRS report that uses an SP as a Dataset. The SP creates a Temp Table, inserts a bunch of data into it, and select's it back out for SSRS to report. Pretty straight forward.

Question:

If multiple users run the report with different parameters selected, will the temp table created by the SP collide in the tempdb and potentially not return the data set expected?

Answer 1

Most likely not. If the temp table is defined as #temp or @temp, then you're safe, as those kind of temp tables can only be accessed by the creating connection, and will only last for the duration of the execution of the stored procedure. If, however, you're using ##temp tables (two "pound" signs), while those tables also only last for as long as the creating stored procedure runs, they are exposed to and accessible by all connections to that SQL instance.

Odds are good that you're not using ##tables, so you are probably safe.

Answer 2

A temp table with a single # is a local temporary table and its scope is limited to the session that created it, so collisions should not be a problem.