Why is random password generation the same when it's inside while loop? TSQL

Question

I have a random password generator. It works fine by itself. When I put it inside of a while loop, I get the same password even though the seed appears to be different.

declare @count int = 0
declare @end int = 500

while (@count < @end)
    BEGIN
    DECLARE @r varchar(6)
    select @r = coalesce(@r, '') +char(
    case when r between 0 and 9 then 48
    when r between 10 and 35 then 55
    else 61 end + r)
    from master..spt_values
    cross join 
    (SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a
    where type = 'P' and number < 6

    print(newId())
    print(@r)

    set @count = @count + 1
END

This produces an output like the following:
As you can see, the guid is different every time, but the password is the same.
How can I make the password different with each iteration?

Answer 1

You need to clear the value of @r for each loop.

declare @r varchar(6);
set @r = ''; -- Clear previous value
select @r = coalesce(@r, '') + char ...

I can see that you thought declare @r varchar(6); would do that for you but it does not.

Have a look at Variables scope which are defined within a while block in stored procedures - SQl Server for more fun with variable declarations.