In MS SQL Server 2005, what happens when a temp table is being accessed by different executions of the same SP?

Question

I have a stored procedure that first checks for a temp table (#temp_table), deletes it if it exists, uses the table, then finally drops it when it's done. This SP is called randomly when a user does something to trigger it and it is likely that sometimes the SP will be executed at the same time - or with some overlap.

Lets say I have Exec1 and Exec2 both of the same SP creating, altering, and dropping the #temp table, and they are running within milliseconds of each other.

What happens? Will Exec1 lock the #temp_table and Exec2 waits while Exec1 finishes? That would obviously be desirable in my case. I wouldn't want both Exec1 & 2 to use the table at the sametime, nor would I want Exec2 fail because Exec1 is already using the table.

[EDIT] Should I just convert my temp table to a table variable?

Answer 1

In sql server if you create a local temp table it is with a single # sign sql server uses a few under-score and some ID in the back end. Say you create a Temp table with the name #Temp sql server in temp db Creates a Table with name #Temp_______10912098 , every Temp table created in separate connections will have their on ID in the end of the name.

These are all the Temp Tables Created in different Connections all has the name #Temp but are appended with some underscores and a unique id sql server uses to differentiate between them.