Generate random characters in stored procedure

I'm trying to create a stored procedure to randomly generate two unique alphanumeric characters from the following character set on SQL Server 2016: ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789 (35-character set). The random values will then be stored on a custom column on the AspNetUsers table.

Currently, only 132 unique 2-character values are stored on the AspNetUsers table, while there can be a total of 1,225 permutations. This is based on the permutations formula: n! / (n - r)! or 35! / (35 - 2)! = 1225.

The following code, based on this example works sometimes, while other times, it becomes an infinite loop. The outer WHILE ensures the value is unique on the table where it will be stored.

DECLARE @length int, @charpool varchar(256), @poollength int, 
    @loopcount int, @randomstring varchar(100)
SET @Length = 2

-- define allowable character explicitly - easy to read this way an easy to 
SET @CharPool = 'ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789'
SET @PoolLength = Len(@CharPool)

SET @LoopCount = 0
SET @RandomString = ''

WHILE (SELECT count(*) FROM AspNetUsers anu WHERE anu.CustomId = @randomstring) > 0 BEGIN
    WHILE (@LoopCount < @Length) BEGIN
        SELECT @RandomString = @RandomString + 
            SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength) + 1, 1)
        SELECT @LoopCount = @LoopCount + 1
    END
END
PRINT @randomstring

You can do something like this:

-- Seed existing
DROP TABLE IF EXISTS #aspusers 
CREATE TABLE #aspusers (id int IDENTITY, somerandomValue varchar(2) UNIQUE)

DECLARE @length int, @charpool varchar(256), @poollength int, 
    @loopcount int, @randomstring varchar(100)
SET @Length = 2

-- define allowable character explicitly - easy to read this way an easy to 
SET @CharPool = 'ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789'

;WITH data AS (
    SELECT  TOP(len(@charpool)) row_number() OVER(ORDER BY @@spid) AS rank
        FROM    sys.objects so
    )
INSERT INTO #aspusers (somerandomValue)
SELECT  TOP 132 chrs
FROM    (
    SELECT  substring(@charpool, d.rank, 1) + substring(@charpool, d2.rank, 1) AS chrs
    FROM    data d
    CROSS JOIN data d2
    ) x
ORDER BY NEWID()

-- Generate new random value
;WITH data AS (
    SELECT  TOP(len(@charpool)) row_number() OVER(ORDER BY @@spid) AS rank
        FROM    sys.objects so
    )
SELECT  TOP 1 @randomstring = x.chrs
FROM    data d
CROSS JOIN data d2
CROSS apply (
        SELECT  substring(@charpool, d.rank, 1) + substring(@charpool, d2.rank, 1) AS chrs
    ) x
WHERE   NOT EXISTS(
        SELECT  1
        FROM    #aspusers asp
        WHERE   asp.somerandomValue = x.chrs
    )
ORDER BY NEWID()

-- Output
SELECT  @randomstring, *
FROM    #aspusers

The main idea is to generate a list of numbers between 1 and len(charpool) which is then used to create the combination of characters using SUBSTRING.

To generate a new random value, one just have to add a WHERE NOT EXISTS which checks the original table.

The whole sys.objects thing can be replaced with GENERATE_SERIES if you're on sufficiently new sql server version.