SQL stored procedure generated by three random passwords

This article shares several SQL stored procedures about the generation of three types of random passwords. If you need to learn, refer to the following.

This article shares several SQL stored procedures about the generation of three types of random passwords. If you need to learn, refer to the following.

Generate a random password. Insus. NET summarizes three passwords and writes them into the stored procedure.

First,

The Code is as follows:

Usp_RandomPassword create procedure [dbo]. [usp_RandomPassword] ( @ Length INT = 8 ) AS BEGIN DECLARE @ RandomPassword NVARCHAR (MAX) = n', @ l int = 1 WHILE @ L <= @ Length -- cycle password Length BEGIN -- Each character is randomly generated with an ASCII code of 48 to 122. DECLARE @ RndChar CHAR (1) = CHAR (ROUND (RAND () * (122-48 + 1) + )) -- Randomly generated characters do not include the following characters If ascii (@ RndChar) not in (58,59, 60,61, 62,63, 64,91, 92,93, 94,95, 96) --:,;, <, =,> ,? , @, [,], ^ ,_,' BEGIN SET @ RandomPassword = @ RandomPassword + @ RndChar SET @ L = @ L + 1 END END SELECT @ RandomPassword END

Second,

The Code is as follows:

Usp_RandomPassword create procedure [dbo]. [usp_RandomPassword] ( @ Length INT = 8 ) AS BEGIN DECLARE @ RandomPassword NVARCHAR (MAX) = n', @ l int = 1 -- The random password will be generated by the following string, numbers 0-9, uppercase letters A-Z, lowercase letters a-z DECLARE @ BaseString VARCHAR (255) = '0123456789abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy' WHILE @ L <= @ Length -- cycle password Length BEGIN -- 61 is the length of the variable @ BaseString minus one SET @ RandomPassword = @ RandomPassword + SUBSTRING (@ BaseString, CONVERT (INT, ROUND (RAND () * 61 + 1, 0), 1) SET @ L = @ L + 1 END SELECT @ RandomPassword END

Third,

The Code is as follows:

Usp_RandomPassword create procedure [dbo]. [usp_RandomPassword] ( @ Length INT = 8 ) AS BEGIN DECLARE @ RandomPassword NVARCHAR (MAX) = N'' DECLARE @ r tinyint, @ l int = 1 WHILE @ L <= @ Length -- cycle password Length BEGIN SET @ R = ROUND (RAND () * 2, 0) -- random generation of 0, 1, 2 integers IF @ R = 0 -- when the variable is 0, a random number is generated. SET @ RandomPassword = @ RandomPassword + CHAR (ROUND (RAND () * 9 + 48, 0 )) Else if @ R = 1 -- when the variable is 1, a random uppercase letter is generated. SET @ RandomPassword = @ RandomPassword + CHAR (ROUND (RAND () * 25 + 65,0 )) Else if @ R = 2 -- when the variable is 2, a lowercase letter is randomly generated. SET @ RandomPassword = @ RandomPassword + CHAR (ROUND (RAND () * 25 + 97,0 )) SET @ L = @ L + 1 END SELECT @ RandomPassword END

The last one can also be rewritten:

The Code is as follows:

Usp_RandomPassword create procedure [dbo]. [usp_RandomPassword] ( @ Length INT = 8 ) AS BEGIN DECLARE @ RandomPassword NVARCHAR (MAX) = n', @ l int = 1 WHILE @ L <= @ Length -- cycle password Length BEGIN DECLARE @ r int = ROUND (RAND () * 2, 0) SET @ RandomPassword = @ RandomPassword + CASE @ R WHEN 0 then char (ROUND (RAND () * 9 + 48, 0 )) WHEN 1 then char (ROUND (RAND () * 25 + 65,0 )) WHEN 2 then char (ROUND (RAND () * 25 + 97,0) END SET @ L = @ L + 1 END SELECT @ RandomPassword END Also reference: