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
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