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

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).

---

Needed this answer myself and from the link provided by David Moye, decided on this and thought it might be of use to others with the same question:

CREATE PROCEDURE ...
AS
BEGIN
  BEGIN TRANSACTION

  -- lock table "a" till end of transaction
  SELECT ...
  FROM a
  WITH (TABLOCK, HOLDLOCK)
  WHERE ...

  -- do some other stuff (including inserting/updating table "a")



  -- release lock
  COMMIT TRANSACTION
END

---

BEGIN TRANSACTION

select top 1 *
from table1
with (tablock, holdlock)

-- You do lots of things here

COMMIT

This will hold the 'table lock' until the end of your current "transaction".

---

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