sp to create unique id if a new value is found code example

Example 1: sp to create unique id if a new value is found

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

Example 2: sp to create unique id if a new value is found

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

Example 3: sp to create unique id if a new value is found

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