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


    set @count = @count + 1

This produces an output like the following: enter image description here
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?


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.

  • So you kept appending to a varchar that had already reached its maximum lenght. – Gerard H. Pille May 18 '18 at 13:27
  • I think you can declare @r outside the loop. And set inside. – paparazzo May 18 '18 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.