Use list of values in procedure-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Here is an example of doing this is a procedure using order by newid. Pretty straight forward and simple.

create or alter procedure updateNaturePassword
(
    @_email AS VARCHAR(256)
) as

    set nocount on;
    
    DECLARE @hashedPasswordList as table 
    (
        HashedPassword varchar(128)
    )

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

    UPDATE accounts
    SET passwd = (select top 1 hashedPassword from @hashedPasswordList order by newid())
    WHERE email = @_email