Adding new customers

SQL Remote User's Guide
SQL Remote Design for Adaptive Server Enterprise
Ensuring unique primary keys

Adding new customers

When a sales representative wants to add a new customer to the Customer table, the primary key value to be inserted is obtained using a stored procedure. This example shows a stored procedure to supply the primary key value, and also illustrates a stored procedure to carry out the INSERT.

The procedures takes advantage of the fact that the Sales Rep identifier is the CURRENT PUBLISHER of the remote database.

NewKey procedure The NewKey procedure supplies an integer value from the key pool and deletes the value from the pool.

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.