Q
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:

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

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.

Meet all of our SQL Server experts

View all SQL Server questions and answers

Start the conversation

Send me notifications when other members comment.

Register

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close