Generate Random Password Using SQL Stored Procedure

This is a small blog on how to generate random password in sql server stored procedure itself. 
 
Sometime we need to generate password on our own without knowing user what it will be.
 
Below is very simple SQL code which will help you to generate a strong random password.
  1. CREATE PROCEDURE GENERATEPWD(@OUTMESSAGE VARCHAR(10) OUTPUT)  
  2. AS   
  3. BEGIN  
  4. SET NOCOUNT ON  
  5. declare @LENGTH INT,@CharPool varchar(26),@PoolLength varchar(26),@LoopCount  INT  
  6. DECLARE @RandomString VARCHAR(10),@CHARPOOLINT VARCHAR(9)  
  7.   
  8.     
  9. SET @CharPool = 'A!B@C!D#E@FG#H$IJ$K%LM%N*PQR%ST&U*V(W)X_YZ'  
  10. DECLARE @TMPSTR VARCHAR(3)  

  11. SET @PoolLength = DataLength(@CharPool)  
  12. SET @LoopCount = 0  
  13. SET @RandomString = ''  
  14.   
  15.     WHILE (@LoopCount <10)  
  16.     BEGIN  
  17.         SET @TMPSTR =   SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength), 1)           
  18.         SELECT @RandomString  = @RandomString + CONVERT(VARCHAR(5), CONVERT(INT, RAND() * 10))  
  19.         IF(DATALENGTH(@TMPSTR) > 0)  
  20.         BEGIN   
  21.             SELECT @RandomString = @RandomString + @TMPSTR    
  22.             SELECT @LoopCount = @LoopCount + 1  
  23.         END  
  24.     END  
  25.     SET @LOOPCOUNT = 0    
  26.     SET @OUTMESSAGE=@RandomString  
  27. END  
Above procedure will return 10 digit length of alpha-numeric password.
 
PARAMETER EXPLANATION
 
@CharPool = Contain alphabets (Random you can change as you wish)
 
You can edit this character pool to include numbers in the password if you want. 
 
You can see a while loop running up to count of 10.
 
Inside that loop we are fetching 1 character randomly from character pool and appends it to a variable at each iteration.
 
Finally it will assign the result to @OUTMESSAGE variable which is of type VARCHAR(10) OUTPUT.
 
And you will get the random 10 digit long random password very easily.
 
Hope it helps someone.