Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
CREATE PROCEDURE NewKey
@TableName VARCHAR(40),
@Location VARCHAR(6),
@Value INTEGER OUTPUT AS
BEGIN
DECLARE @NumValues INTEGER
SELECT @NumValues = count(*),
@Value = min(value)
FROM KeyPool
WHERE table_name = @TableName
AND location = @Location
IF @NumValues > 1
DELETE FROM KeyPool
WHERE table_name = @TableName
AND value = @Value
ELSE
-- Never take the last value,
-- because RestorePool will not work.
-- The key pool should be kept large
-- enough so this never happens.
SELECT @Value = NULL
END
♦NewCustomerprocedure TheNewCustomer procedure inserts a new
customerinto the table, using the value obtained by NewKey to construct
theprimary key.
CREATE PROCEDURE NewCustomer @name VARCHAR(40),
@loc VARCHAR(6) AS
BEGIN
DECLARE @cust INTEGER
DECLARE @cust_key VARCHAR(12)
EXEC NewKey ’Customer’, @loc, @cust output
SELECT @cust_key = ’cust’ +
convert( VARCHAR(12), @cust )
INSERT INTO Customer (cust_key, name, rep_key )
VALUES ( @cust_key, @name, @loc )
END
Youmay want to enhance this procedure by testing the @cust value
obtainedfrom NewKey to check that it is not NULL, and preventing the
insertif it is NULL.
Testing the keypool
❖Totest the primary key pool
1. Re-extract a remote database using the field_user user ID.
2. Try this sample INSERT at the remote and consolidated sites:
EXEC NewCustomer ’Great White North’, rep1
179