Setting locks in SQL Server 2000

[mike55]

Hi all,

 

Am try to set a readwrite lock on a database table, here is the code that I am using in my procedure:

CREATE PROCEDURE dbo.spReserveCredits 

@Organization as nvarchar,
@Cost as money,
@Result as bit output

AS

UPDLOCK

DECLARE @Amount as money

	SELECT @Amount = Credit 
	FROM SMS_Credit 
	WHERE Org_ID = @Organization

	if @Cost > @Amount
		SET @Result = 0

	else

		begin

			UPDATE SMS_Credit
			SET Credit = @Amount - @Cost
			WHERE Org_ID = @Organization

			SET @Result = 1

			COMMIT

		end

	return @Result

GO

 

I need to lock the table so that I can read and write to it if necessary, while at the same time preventing anyone else from viewing the table.

 

The error that I am getting is: "Incorrect Syntax near UPDLOCK".

 

Mike55

[michael_hk]

First I must thank you million times for posting this Q. :D

 

Like you, I was encountering a locking Q these days and can't figure it out (Details). Only after reading your post, I realized UPDLOCK is exactly what I was looking for. Thanks.

 

OK, back to your question. Locking hints is not used in this way, it should be like

 

 

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors [b]WITH (UPDLOCK)[/b]
GO

Example from SQL Books Online -> locking -> hints

[mike55]

Thanks a million, for the reply - have been banging my head against a brick wall for the last half day trying to figure out how to use locks.

 

Mike55