greatseats
asked on
Updating Table Column with Random Passwords
I have a stored Procedure that generates random passwords. I have a user table with a password column. Some users have Null or Empty password fields. I would like to write a script that fills these empty or NULL fields with a randomly generated password from the stored procedure
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Assuming the SP returns the password as an output parameter, something like this will do it.
Alternatively you coudl load up a table with passwords generated by your SP and then apply them.
Alternatively you coudl load up a table with passwords generated by your SP and then apply them.
DECLARE @Password VARCHAR(100)
DECLARE @UserName VARCHAR(100)
SELECT @UserName = TOP 1 UserNameColumn FROM YourTable WHERE PasswordColumn IS NULL -- seed the loop
WHILE @UserName IS NOT NULL
BEGIN
EXEC p_YourProcedure @Password OUTPUT -- get the next random password
-- Apply the password (assuming UserNameColumn is unique)
UPDATE YourTable
SET PasswordColumn = @Password
WHERE UserNameColumn = @UserName
SELECT @UserName = TOP 1 UserNameColumn FROM YourTable WHERE PasswordColumn IS NULL
END
ASKER
I get the following error when i try to execute this script.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TOP'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'TOP'.
do you know what this is about ?
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TOP'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'TOP'.
do you know what this is about ?
DECLARE @Password VARCHAR(100)
DECLARE @UserName VARCHAR(100)
SELECT @UserName = TOP 1 UserNameColumn FROM YourTable WHERE PasswordColumn IS NULL -- seed the loop
WHILE @UserName IS NOT NULL
BEGIN
EXEC p_YourProcedure @Password OUTPUT -- get the next random password
-- Apply the password (assuming UserNameColumn is unique)
UPDATE YourTable
SET PasswordColumn = @Password
WHERE UserNameColumn = @UserName
SELECT @UserName = TOP 1 UserNameColumn FROM YourTable WHERE PasswordColumn IS NULL
END
It has to be like in my comment.
SELECT TOP 1 @userid = userid FROM users WHERE IsNull(password, '') = ''
The TOP 1 must come before the assignment to variable.
SELECT TOP 1 @userid = userid FROM users WHERE IsNull(password, '') = ''
The TOP 1 must come before the assignment to variable.
Here is a blend of the approaches since you liked that one. Since you said password can be empty or NULL I would also change to my approach of comparing to empty string after changing nulls to '' that way you catch both.
DECLARE @Password VARCHAR(100)
DECLARE @UserName VARCHAR(100)
SELECT TOP 1 @UserName = UserNameColumn FROM YourTable WHERE IsNull(PasswordColumn, '') = ''
WHILE @UserName IS NOT NULL
BEGIN
EXEC p_YourProcedure @Password OUTPUT -- get the next random password
-- Apply the password (assuming UserNameColumn is unique)
UPDATE YourTable
SET PasswordColumn = @Password
WHERE UserNameColumn = @UserName
-- grab next username
SELECT TOP 1 @UserName = UserNameColumn FROM YourTable WHERE IsNull(PasswordColumn, '') = ''
END
Sorry I got the TOP 1 around the wrong way. Its not needed anyway.
ASKER
I really like this UDF approach, fast, clean and simple - well I had to learn more about UDF and how to create a udf password generator :-) - fun ! I am still trying to get the other SP approaches to work - running into a few issues - Thanks very much for the assistance
Open in new window