Generate Random Number Using StoredProcedure

Whenever a user registers to a website, it will generate a random password for that user. One can make use of this Stored Procedure to create passwords.

One can make use of this Stored Procedure when a user requests for a password through forget password link.

Execute the below SP to generate random password.

This Stored Procedure has two input parameters;

1) @PasswordType :- This is nothing but a type of password, like easy,hard..
if a user give the input as 'easy', it will generate a easy password.
if a user give the input as 'hard' it will generate a tough password.

2) @length :- This is the length of the random password that the user is
going to generate.

The below procedure can be called with the following options;

Option 1:
EXEC Random_Password_Generator

Option 2:
EXEC Random_Password_Generator @PasswordType = 'easy', @length = 8

Option 3:
EXEC Random_Password_Generator @PasswordType = 'hard', @length = 8

The above statement will create a tough random password with the length of 8 characters.

Here is the Stored Procedure;

CREATE PROC Random_Password_Generator
(
@PasswordType CHAR(4)
,@length INT(6)
)
AS
SET NOCOUNT ON
DECLARE @RandomPassword VARCHAR(100)
,@Num TINYINT
,@VowelsToMix CHAR(6)
SET @RandomPassword = ''
SET @VowelsToMix = 'oiauex' --added x to make it as length of 6 characters--Looping out the values to geberate random password
WHILE ( @length > 0 )
BEGIN
IF ( @PasswordType = 'easy' ) --Generate Easy Password
BEGIN
IF (( @length % 2 ) = 0 )
SET @RandomPassword = @RandomPassword + SUBSTRING(@VowelsToMix,CONVERT(INT,ROUND(1 + (RAND() * 5),0)),1)
ELSE
SET @RandomPassword = @RandomPassword + CHAR(ROUND(97 + (RAND() * 25),0))
END
ELSE --Generate Tough Password
BEGIN
SET @Num = ROUND(1 + (RAND() * 3),0)
IF ( @Num = 1 ) --Adding a random lower case alphabet to @RandomPassword
SET @RandomPassword = @RandomPassword + CHAR(ROUND(97 + (RAND() * 25,0))
ELSE IF ( @Num = 2 ) --Adding a random upper case alphabet to @RandomPassword
SET @RandomPassword = @RandomPassword + CHAR(ROUND(65 + (RAND() * 25,0))
ELSE IF ( @Num = 3 ) --Adding a random number to @RandomPassword
SET @RandomPassword = @RandomPassword + CHAR(ROUND(48 + (RAND() * 9,0))
ELSE IF ( @Num = 4 ) --Adding a random special characters to @RandomPassword
SET @RandomPassword = @RandomPassword + CHAR(ROUND(33 + (RAND() * 13,0))
END
SET @length = @length - 1
END
SELECT @RandomPassword
SET NOCOUNT OFF
GO