Problem solve Get help with specific problems with your technologies, process and projects.

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:


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
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 last published in September 2003

Dig Deeper on SQL Server Stored Procedures

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.