Use list of values in procedure

Question

I'm trying to create a stored procedure to update a row with a list of pre determined values for example:

CREATE PROCEDURE updateNaturePassword(@_email AS VARCHAR(256))
AS
DECLARE 
    SET @hashedPasswordList = '["{bcrypt}$2a$10",{bcrypt}$2a$10$Kh8YS.","{bcrypt}$2a$10$safds","{bcrypt}$2a$10$Wl8ZKTF2YGobQ6yi"]'; 
    SET randomIndexValue = (look at the list size and gets a random index);
    SET hashedPassword = hashed_password_list[randomIndexValue];;
BEGIN
    UPDATE accounts 
    SET passwd = hashed_password 
    WHERE lower (email) =lower (@_email);
END;

The easier thing is to create a table, insert these random passwords and select them but I cannot create another table.

Any ideas? sorry if this is very simple but I'm just starting to work with databases.

Answer 1

If the password string was an argument, then perhaps STRING_SPLIT. Instead, I start with a local table. This is not a procedure yet, but it can be adapted. Also, the use of lower fn in the where will prevent the the use of an index - research sargable if interested. I removed the fn, but if your database is case sensitive, it would be time to really think about life and performance.

DECLARE @_email AS VARCHAR(128)

DECLARE @hashedPasswordList as table (
    ID int IDENTITY,
    HashedPassword varchar(128)
)

DECLARE @randomIndex int
DECLARE @hashedPassword varchar(128)

INSERT INTO @hashedPasswordList (HashedPassword) VALUES ('{bcrypt}$2a$10'),('{bcrypt}$2a$10$Kh8YS.'),('{bcrypt}$2a$10$safds'),('{bcrypt}$2a$10$Wl8ZKTF2YGobQ6yi')

SET @randomIndex = 1 + FLOOR( (SELECT COUNT(*) FROM @hashedPasswordList) * RAND() )
SELECT @hashedPassword = HashedPassword FROM @hashedPasswordList WHERE ID = @randomIndex

UPDATE accounts 
SET passwd = @hashedPassword
WHERE email = @_email