Question

I have declared a procedure which creates a temporary table like:

create table #parents(
    id integer null,
    )

now there is something like an insert into #parents using the result of a different procedure, let's say GetSpecialParents.

The GetSpecialParents procedure uses again a statement like

create table #parents(
    id integer null,
    )

Will the two temporary table definitions conflict with each other? Will both procedures use the same table, or will the tables be local to the procedure which created it?

So do I have to make sure, that all temporary tables which might be called in a sequence of procedures have a unique name?

Answer 1

You (probably) don't have to worry about unique names.

There are four ways to create a local temporary table, and all of them have slightly different rules: CREATE TABLE #t, CREATE LOCAL TEMPORARY TABLE t, DECLARE LOCAL TEMPORARY TABLE t and SELECT INTO #t.

For CREATE TABLE #t, the table has nested scope and persistence.

If the CREATE TABLE #t is executed outside of any BEGIN block, the table name is visible to throughout all code running on that connection (unless overridden; see next paragraph), and the data persists until the table is explicitly dropped or the connection ends.

If the statement is executed within a BEGIN block, the table name is visible from that point on within the block, and the data persists, until the END is reached... at which point the table is dropped. If the table name is the same as one created outside the BEGIN, the outer table is not visible after the inner CREATE is executed... but the outer table becomes visible again after the END is reached.

Note that this form of CREATE doesn't have a COMMIT as a side effect. Also note the ON COMMIT clause can't be coded, but the commit action is the same as ON COMMIT PRESERVE ROWS.

In other words, CREATE TABLE #t works the way it should :)