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

by

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

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Only inserting a row if it’s not already there
  3. Function vs. Stored Procedure in SQL Server
  4. What is a stored procedure?
  5. How to assign an exec result to a sql variable?
  6. SQL Server SELECT into existing table
  7. How to check if a stored procedure exists before creating it
  8. Stored procedure that exports data into csv files only exports to one file
  9. Inserting rows into a table with one IDENTITY column only [duplicate]
  10. Eliminate and reduce overlapping date ranges
  11. Is it possible to write single query on two table which are not connected to each other?
  12. Create a date from day month and year with T-SQL
  13. How should I pass a table name into a stored proc?
  14. Creating an index on a table variable
  15. Where value in column containing comma delimited values
  16. What is the difference between using a cross join and putting a comma between the two tables?
  17. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  18. Search text in stored procedure in SQL Server
  19. Pivot using SQL Server 2000
  20. Sequence vs identity
  21. How to update and order by using ms sql
  22. How to store image in SQL Server database tables column [duplicate]
  23. Procedure or function !!! has too many arguments specified
  24. Can I set ignore_dup_key on for a primary key?
  25. SQL Server replace, remove all after certain character
  26. How to find column names for all tables in all databases in SQL Server
  27. SQL Row_Number() function in Where Clause
  28. When using GETDATE() in many places, is it better to use a variable?
  29. COALESCE Function in TSQL
  30. Convert bigint to datetime

Leave a Comment