Alex Rivera | Logout

SQL Server - How to lock a table until a stored procedure finishes

Asked 2010-09-07T21:06:24.627
71

I want to do this:

create procedure A as
  lock table a
  -- do some stuff unrelated to a to prepare to update a
  -- update a
  unlock table a
  return table b

Is something like that possible?

Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished. (I'm not able to change procedure A to return table a).

Edit
Report

1 Answer

20

Use the TABLOCKX lock hint for your transaction. See this article for more information on locking.

answered 2010-09-07T21:13:00.320

Your Answer