2.framework-sp_v001.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

-- -- --
-- SP --
-- -- --


--
-- NEXT KEY
--

/*
 * Key allocation stored procedure (needed for custom records locking).
 * ====================================================================
 *
 * Call format: QX_NEXT_KEY(TABLE, INCREMENT) ==> NEXT_PRIMARY_KEY
 * NOTE. 
 * Procedure behavior depends on INCREMENT parameter value:
 *   INCREMENT = 1 - returns the next key value, 
 *   INCREMENT < 1 - returns the "fake" value (is NOT saved in table),
 *   INCREMENT > 1 - returns the value and "reserves" the range [OLD_VALUE..NEW_VALUE] of values.
 */
CREATE PROCEDURE dbo.QX_NEXT_KEY (
	@TABLE VARCHAR(20),
	@INCREMENT INT,
	@RET_KEY INT OUTPUT
) AS

	DECLARE @INCR INT
	DECLARE @DOCOMMIT BIT

BEGIN 

	-- Check increment.
	IF @INCREMENT <= 0 
		BEGIN
			SET @INCR = 1
			SET @DOCOMMIT = 0
		END
	ELSE 
		BEGIN
			SET @INCR = @INCREMENT
			SET @DOCOMMIT = 1
		END
	
	BEGIN TRANSACTION

		-- Set a new key value.
		UPDATE QX_KEYS
		SET QX_KEYS.KEYVALUE = QX_KEYS.KEYVALUE + @INCR 
		WHERE QX_KEYS.TABLENAME = @TABLE

		-- Not found - add a new table/key pair.
		IF (@@ROWCOUNT = 0)
			INSERT INTO QX_KEYS(TABLENAME, KEYVALUE) VALUES (@TABLE, @INCR)
		
		-- Return the old key value + 1.
		-- It reserves the range [OLD_VALUE..NEW_VALUE] of values, if INCR > 1.
		SELECT @RET_KEY = QX_KEYS.KEYVALUE - @INCR + 1 FROM QX_KEYS
		WHERE QX_KEYS.TABLENAME = @TABLE

	IF (@DOCOMMIT = 1)
		COMMIT TRANSACTION
	ELSE 
		ROLLBACK TRANSACTION

END

IF @@ERROR != 0
	RETURN (1)
ELSE
	RETURN (0)

go


grant execute on QX_NEXT_KEY to public
go