User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
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
♦ NewCustomer procedure The NewCustomer procedure inserts a new
customer into the table, using the value obtained by NewKey to construct
the primary 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
You may want to enhance this procedure by testing the @cust value
obtained from NewKey to check that it is not NULL, and preventing the
insert if it is NULL.
Testing the key pool
❖ To test 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