ALTER PROC sp_CubeLock
	@AType		INT,  -- 0 - Read, 1 - Write
 
	@ACubeID	uniqueidentifier,
	@Result		INT OUTPUT
AS
SET NOCOUNT ON

DECLARE @pettytype INT, 
	@type INT, 
	@logintime DATETIME

CREATE TABLE #locks (
	cube_id		UNIQUEIDENTIFIER,
	locktype	TINYINT,
	spid		SMALLINT,
	sid		BINARY( 85 ),
	login_time	DATETIME,
	login_name	NCHAR( 128 ),
	hostname	NCHAR( 128 )
)

EXEC sp_CubeLockVerify

BEGIN TRAN

	IF (@AType =  0 ) 
	BEGIN	 -- Требуется доступ на чтение - проверяем блокировку на запись
 
		SET @pettytype	=  0 
		SET @type	=  1 
	END
	ELSE 	 -- Требуется доступ на запись - проверяем все блокировки
 
		SET @pettytype 	=  1 
	 -- Блокируем таблицу блокировок :-)
 
	SELECT * FROM XCubeLock WITH (TABLOCKX)

	SELECT @logintime = login_time FROM master.dbo.sysprocesses WHERE spid = @@SPID
					
	 -- Вытаскиваем блокировки указанного дерева
 
	INSERT INTO #locks
	SELECT 	CL.cube_id,
		CL.locktype,
		CL.spid, 
		SP.sid,
		CL.login_time,
		SP.loginame AS login_name,
		SP.hostname
	FROM	XCubeLock CL
		INNER JOIN
		master.dbo.sysprocesses SP 
			ON (SP.spid = CL.spid AND SP.login_time = CL.login_time)
	WHERE 	 CL.cube_id = @ACubeID AND
		(CL.locktype = @type OR @pettytype =  1 )
			
	IF (@@ROWCOUNT =  0 )
	BEGIN
		INSERT INTO XCubeLock
			(spid, login_time, cube_id, locktype)
		VALUES	(@@SPID, @logintime, @ACubeID, @AType)
		IF (@@ERROR <>  0 ) 
			SET @result =  0 
		ELSE
			SET @result =  1 
	END
	ELSE
		SET @result =  0 


	SELECT * FROM #locks
	DROP TABLE #locks

IF (@result =  1 ) 
	COMMIT TRAN
ELSE BEGIN
	ROLLBACK TRAN
	RAISERROR('Ошибка добавления записи о блокировке',  16 , - 1 )
END
GO

exec sp_cubelock null, null, null

Server: Msg  515 , Level  16 , State  2 , Procedure sp_CubeLock, Line  58 
Cannot insert the value NULL into column 'locktype', table 'XCubeLock'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Server: Msg  50000 , Level  16 , State  1 , Procedure sp_CubeLock, Line  77 
Ошибка добавления записи о блокировке