Restrict simultaneous access to resources

--------------------------------------------------------------------
-- Create the table that holds Semaphore data
--
-- Fields : 
-- SemName          The Name of the semaphore used to identify it
-- MaxInstance      The maximum number of concurrent processes using
--                  this Semaphore
-- CurInstance      The current number of concurrent processes that
--                  are using this Semaphore
-- SemDescription   Free description field
--------------------------------------------------------------------
CREATE TABLE Semaphore
(
SemNameVARCHAR (25) CONSTRAINT PK_Semaphores PRIMARY KEY NOT NULL,
MaxInstance INT NOT NULL,
CurInstance INT NOT NULL CONSTRAINT DF_CurInstance DEFAULT 0,
SemDescription VARCHAR(255) NULL
)

DROP PROCEDURE usp_lockSemaphore
--------------------------------------------------------------------
-- Create the Stored Procedure to take control of the Semaphore
--
-- Parameters :
-- SemName           The name of the Semaphore to take control of
--
-- ReturnValues :
-- 0 (zero)          Access to the protected resource is allowed
-- Negative          Error occurred
-- 1 (one)           Access to the protected resource is not allowed
--------------------------------------------------------------------
CREATE PROCEDURE usp_LockSemaphore
@SemName varchar(25)
AS
DECLARE @iMaxInstance int
DECLARE @iCurInstance int
DECLARE @iReturnValue int
DECLARE @iRowCount    int
-- Start the transaction
BEGIN TRANSACTION
-- Fetch the value
SELECT @iMaxInstance = MaxInstance, @iCurInstance = CurInstance
FROM Semaphore WITH (ROWLOCK)
WHERE SemName = @SemName
SET @iRowCount = @@ROWCOUNT
IF @@ERROR <> 0
BEGIN
   SET @iReturnValue = -1
END
ELSE IF @iRowCount <> 1
BEGIN
   SET @iReturnValue = -2
END
ELSE
    -- Check the value
    IF @iMaxInstance > @iCurInstance 
    BEGIN
    -- Update the value
UPDATE Semaphore
        SET CurInstance = @iCurInstance + 1
        WHERE SemName = @SemName
        -- Set return value
        SET @iReturnValue = 0
    END
    ELSE
SET @iReturnValue = 1
-- End the transaction
COMMIT TRANSACTION
-- Return from the procedure
RETURN @iReturnValue
GO

--------------------------------------------------------------------
-- Create the Stored Procedure to release control over the Semaphore
--
-- Parameters :
-- SemName           The name of the Semaphore to release
--
-- ReturnValues :
-- 0 (zero)          Success 
-- Nonzero           Failure
--------------------------------------------------------------------
CREATE PROCEDURE usp_UnlockSemaphore
@SemName varchar(25)
AS
DECLARE @iReturnValue int
-- Update the value
UPDATE Semaphore SET CurInstance = CASE CurInstance -1 WHEN -1 THEN 0 ELSE CurInstance - 1 END
WHERE SemName = @SemName
IF @@ERROR <> 0
BEGIN
   SET @iReturnValue = -1
END
ELSE
BEGIN
   SET @iReturnValue = 0
END
-- Return from the procedure
RETURN @iReturnValue
GO