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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to locking