Ask the Expert

Create private temp tables for many users to execute same procedure

I have a temporary table in a stored procedure (based on defined columns). If I am executing the procedure and someone else executes that same procedure, it won't allow the other person to execute until I drop the temporary table. This is normal. Is there any way to name the temporary table based on a variable?

For instance, if I am executing the procedure the temp table name will be #xyz_my_id and if someone else executes the same procedure it creates temp table with the name #xyz_his_id. This way both of us can execute the same procedure without waiting for temp table to be dropped.

    Requires Free Membership to View

If you create a private temporary table in your stored procedure many people should be able to execute the procedure simultaneously. By private temp table I mean a table with a single # such as #SalesTable. If you look in tempdb you will see the table actually created contains the name #SalesTable with '_______________SOMEUNIQUEID' appended to it.

This naming convention actually allows one to create private temp tables with the same exact names as your neighbors. While we reference them as #SalesTable internally they are stored with the unique identifier value appended to it. If you create a global temporary table these are actual tables created 'as is' in the tempdb database with a double pound ## naming convention such as ##SalesTable.

With global temp tables you may experience locking and blocking. If you absolutely must use global temp tables you can consider creating the table using a guide as the table name (with the –'s removed of course because they aren't allowed in the table name). In fact, I've created unique tables using the newid() function to generate a uniqueidentifier value.

This was first published in June 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: