CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-procedures
Carl Onager
Carl Onager

Reputation: 4122

Create Unique ID in Stored Procedure to Match Legacy Data

I'm creating CRUD procedures that duplicate a legacy program that generates a unique ID based on a 'Next ID' field in a separate table. Rather than duplicate the use of a separate table I have written a stored procedure that reads the number of rows in the table.

CREATE PROCEDURE [TLA_CreateItem] 
    @SiteReference varchar(50)
    ,@ItemID varchar(4)
    ,@NewUniqueID varchar(68) OUTPUT
AS
BEGIN
    DECLARE @Rows varchar(12)
    SET @Rows = (CONVERT(varchar(12), (SELECT Count(UniqueID) FROM [TLA_Items]) + 1))

    SET @NewUniqueID = @ItemID + @SiteReference + @Rows

    INSERT INTO [TLA_Items] ([ItemID], [UniqueID])
    VALUES (@ItemID, @NewUniqueID)

    SELECT @NewUniqueID
END

I've simplified the code above but what's not shown is that the TLA_Items table also has an IDENTITY column and that it needs to work with SQL Server 2008.

The UniqueID field has to match the pattern of the legacy program: ItemID + SiteReference + (integer representing number of previous records)

However when testing this I've found a flaw in my logic. If rows are deleted then it's possible to create a unique Id which matches an existing row. This doesn't happen in the legacy system as rows are rarely deleted and the separate table stores the next number in the sequence.

Other than store the next ID value in a separate table, is there a better technique, to create a unique ID that matches the legacy pattern?

Upvotes: 2

Views: 3039

Answers (3)

Andriy M
Andriy M

Reputation: 77677

Here is another option, but please bear in mind that it would affect existing UniqueID values.

If you can afford a slight change to the table schema, you could add a column called something like UniqueIDPrefix:

ALTER TABLE dbo.TLA_Items
ADD UniqueIDPrefix varchar(56) NOT NULL;

and redefine the UniqueID column to be a computed column:

ALTER TABLE dbo.TLA_Items
DROP COLUMN UniqueID;

GO

ALTER TABLE dbo.TLA_Items
ADD UniqueID AS UniqueIDPrefix + CAST(IdentiyColumn AS varchar(12));

In your stored procedure, you would then need to populate UniqueIDPrefix instead of UniqueID (with just the result of @ItemID + @SiteReference)

INSERT INTO dbo.[TLA_Items] ([ItemID], [UniqueIDPrefix])
VALUES (@ItemID, @ItemID + @SiteReference);

and read the value of UniqueID using either OUTPUT or SCOPE_IDENTITY(), as in my other answer.

Upvotes: 2

Andriy M
Andriy M

Reputation: 77677

You could have your procedure store only the prefix (@ItemID + @SiteReference) into UniqueID and use a FOR INSERT trigger to append the IDENTITY value as the rows component immediately after the row is inserted, something like this:

CREATE TRIGGER TLA_Items_Adjust
  ON dbo.TLA_Items
  FOR INSERT
AS
BEGIN
  UPDATE t
  SET t.UniqueID = i.UniqueID + CAST(t.IdentityColumn AS varchar(10))
  FROM dbo.TLA_Items AS t
  INNER JOIN inserted AS i
    ON t.IdentityColumn = i.IdentityColumn
  ;
END

To read and return the newly generated UniqueID value as the OUTPUT parameter as well as a row, you could use a table variable and the OUTPUT clause in the INSERT statement, like this:

CREATE PROCEDURE [TLA_CreateItem] 
    @SiteReference varchar(50)
    ,@ItemID varchar(4)
    ,@NewUniqueID varchar(68) OUTPUT
AS
BEGIN
    DECLARE @GeneratedUniqueID TABLE (UniqueID varchar(68));

    INSERT INTO dbo.[TLA_Items] ([ItemID], [UniqueID])
    OUTPUT inserted.UniqueID INTO @GeneratedUniqueID (UniqueID)
    VALUES (@ItemID, @ItemID + @SiteReference);

    SELECT @NewUniqueID = UniqueID FROM @GeneratedUniqueID;

    SELECT @NewUniqueID;
END

Although instead of using OUTPUT you could probably just read the value from the row matching the SCOPE_IDENTITY() result:

CREATE PROCEDURE [TLA_CreateItem] 
    @SiteReference varchar(50)
    ,@ItemID varchar(4)
    ,@NewUniqueID varchar(68) OUTPUT
AS
BEGIN
    INSERT INTO dbo.[TLA_Items] ([ItemID], [UniqueID])
    VALUES (@ItemID, @ItemID + @SiteReference);

    SELECT @NewUniqueID = UniqueID
    FROM dbo.TLA_Items
    WHERE IdentityColumn = SCOPE_IDENTITY();

    SELECT @NewUniqueID;
END

Upvotes: 2

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

It sounds like you are on SQL 2008, but if you were on 2012, you could use a sequence to store an incrementing value.

How about never delete? You could add a flag to the table for logical deletes.

Upvotes: 0

Related Questions