Q

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?

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

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