How do I insert the output of these loops into my table?

Question

Declare @PASS char(16)

Declare @COUNTER int

SET @COUNTER = 0

While @COUNTER < 2

begin

    select @PASS =replace
                  (replace
                     (replace
                        (replace
                           (replace(
                              (select substring(@String, 1 + Number, 1) 
                               from master..spt_values
                               where number < datalength(@String)
                               order by NEWID()
                               for xml path('') 
                              ), '<', '<')
                           ,'>','>')
                        ,'&','&')
                     ,'"','''''')
                  ,'&apos','''')
   select @pass
   set @counter = @counter + 1

end

What are you trying to achieve? Here `SELECT @PASS = REPLACE ...` you are basically assigning a "VARCHAR" (result of the REPLACE-expression) to a "INT" (type of the `@PASS`). Did you mean to do `SELECT @STRING = RELACE ...`? — Christian.K, Sep 25 '15 at 08:40
what is the pass variable. It's a variable that manages the looping process or it's a variable you need to do operation on. It's like you don't know how to use a while. — CiucaS, Sep 25 '15 at 08:40
I have created insert statements for new login users, the password column needs to have randomly generated passwords. So what I am trying to achieve is loop the code that generates the password to help output different passwords for each of the insert statements for the users. I really don't know how to use the while loop. I can't figure it out. please help, thank you. — user1234, Sep 25 '15 at 08:43
Your use of the `WHILE` loop as such is "correct". The statement you execute inside isn't (see my comment above). — Christian.K, Sep 25 '15 at 08:45
I have the loop working now. I just need some help on how to get the output into a insert statement for my table. — user1234, Sep 25 '15 at 11:21

score 3 · Answer 1 · edited Jan 18 '21 at 12:30

Based on your previous question I think you want to generate multiple passwords.

But you can't store it in one variable so use temp table/table variable instead.

Demo

DECLARE @String NVARCHAR(MAX) = 
     N'abcdefghijkmnopqrstuvwxyz' +  --lower letters
      'ABCDEFGHIJKMNOPQRSTUVWXYZ' +  --upper letters
      '0123456789'+  --number characters
      ')[:|!@$&<';

CREATE TABLE #passwords(Id INT IDENTITY(1,1), pass NVARCHAR(100));

DECLARE @i           INT = 0
       ,@pass_number INT = 20; -- max number of passwords

WHILE @i < @pass_number  
BEGIN
    SET @i += 1; 
    INSERT INTO #passwords(pass)
    Select [pass] = CAST(replace
                      (replace
                      (replace
                      (replace
                      (replace(
                      (select
                       substring(@String, 1 + Number, 1) 
                       from master..spt_values
                       where number < datalength(@String)
                       order by NEWID()
                       for xml path('') )
                        ,'&lt;', '<')
                        ,'&gt;','>')
                        ,'&amp;','&')
                        ,'"','''''')
                        ,'&apos','''') AS CHAR(16))
END
                      
SELECT *
FROM #passwords;

Also you probably want to replace like this:

,'&lt;', '<')
,'&gt;','>')
,'&amp;','&')

Yes, that looks like what to OP "wanted" to have ;-) – Christian.K Sep 25 '15 at 08:47 — Christian.K, Sep 25 '15 at 08:47

How do I insert the output of these loops into my table?

1 Answers1