Q

Create private temp tables for many users to execute same procedure

A naming convention in a stored procedure allows you and your neighbors to execute the procedure simultaneously, according to SQL Server Expert Joe Toscano.

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.

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

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close