Generate new Random ID, unique not duplicated in two columns in two tables.
This was created by AI, then I just copied and pasted with minor modifications to fit my needs.
CREATE PROCEDURE [dbo].[GenerateUniqueRandomID]
AS
BEGIN
DECLARE @RandomID NVARCHAR(12)
DECLARE @ExistingCount INT
SET @RandomID = ( SELECT LEFT(CONVERT(NVARCHAR(36), CONVERT(NVARCHAR(36), Replace(NEWID(), '-', ''))), 12) ) -- Generate a random ID
SELECT @ExistingCount = COUNT(*) -- Check if the ID exists in both tables
FROM (
SELECT PostID As ID FROM Posts
UNION ALL
SELECT UserID As ID FROM Users
) AS CombinedIDs
WHERE ID = @RandomID
WHILE @ExistingCount > 0 -- If the ID already exists, regenerate
BEGIN
SET @RandomID = ( SELECT LEFT(CONVERT(NVARCHAR(36), CONVERT(NVARCHAR(36), Replace(NEWID(), '-', ''))), 12) )
SELECT @ExistingCount = COUNT(*) FROM (
SELECT PostID as ID FROM Posts
UNION ALL
SELECT UserID As ID FROM Users
) AS CombinedIDs
WHERE ID = @RandomID
END
SELECT @RandomID AS UniqueRandomID -- Return the unique random ID
Union All
SELECT 'U' + Convert(nvarchar, Convert(int , RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) * 100000000 )) As UniqueRandomID
END
AS
BEGIN
DECLARE @RandomID NVARCHAR(12)
DECLARE @ExistingCount INT
SET @RandomID = ( SELECT LEFT(CONVERT(NVARCHAR(36), CONVERT(NVARCHAR(36), Replace(NEWID(), '-', ''))), 12) ) -- Generate a random ID
SELECT @ExistingCount = COUNT(*) -- Check if the ID exists in both tables
FROM (
SELECT PostID As ID FROM Posts
UNION ALL
SELECT UserID As ID FROM Users
) AS CombinedIDs
WHERE ID = @RandomID
WHILE @ExistingCount > 0 -- If the ID already exists, regenerate
BEGIN
SET @RandomID = ( SELECT LEFT(CONVERT(NVARCHAR(36), CONVERT(NVARCHAR(36), Replace(NEWID(), '-', ''))), 12) )
SELECT @ExistingCount = COUNT(*) FROM (
SELECT PostID as ID FROM Posts
UNION ALL
SELECT UserID As ID FROM Users
) AS CombinedIDs
WHERE ID = @RandomID
END
SELECT @RandomID AS UniqueRandomID -- Return the unique random ID
Union All
SELECT 'U' + Convert(nvarchar, Convert(int , RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) * 100000000 )) As UniqueRandomID
END
Views 245
Downloads 50
CodeID
DB ID
ANmarAmdeen
614
Revisions
v1.0
Sunday
February
18
2024