SQL Stored procedure generated by three random passwords

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

First,

The code is as follows:

Copy code

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:

Copy code

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:

Copy code

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:

Copy code

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: