Ask the Expert

Locking a temp table in a stored procedure

I am using a stored procedure to update a table. In the stored procedure I am creating a temp table, #TempCost, to do some calculations to update the main table. The application for which I am writing the stored procedure is a multi-user one, so the same stored procedure, and in turn the same temp table, can be accessed by many users at a time, which can result in the malfunctioning of the application. So to avoid that situation, I want to lock the temp table. How can I lock it?

    Requires Free Membership to View

The answer is to not use a temp table at all. This query can be written AND run much more efficiently by using what is referred to as a virtual table. It looks something like this:

BEFORE:

Select col1, sum(col2) testsum
Into #temp
>From table1
Group by col1

Select col1, col4, sum(testsum) secondaryagg
>From table2 inner join #temp on table2.col1 = #temp.col1
Group by col1, col4
VIRTUAL TABLE:
Select col1, col4, sum(testsum) secondaryagg
>From table2 inner join 
 (Select col1, sum(col2) testsum
  From table1
  Group by col1) a on table2.col1 = a.col1
Group by col1, col4

For More Information

This was first published in September 2003

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: