DROP FUNCTION IF EXISTS dev.fnGeneratePassword
GO

CREATE FUNCTION dev.fnGeneratePassword (
   @length int
) RETURNS nvarchar(50) 
AS BEGIN

  DECLARE 
     @digit nvarchar(10) = '23456789'
    ,@upper nvarchar(26) = 'ABCDEFGHJKLMNPQRSTUVWXYZ'
    ,@lower nvarchar(26) = 'abcdefghijkmnopqrstuvwxyz'
    ,@character nvarchar(120)
    ,@i int = 1
    ,@return nvarchar(50) = ''
    ,@format nvarchar(50)
    ,@char char(1)
  
  IF @length < 8 BEGIN
    SET @length = 8
  END

  SET @character = @lower + @digit + @upper
  SET @format = REPLICATE('C', @length)
  
  SET @format = STUFF(@format, (SELECT (CAST(FLOOR(RandomNumber * 100) AS int) % 2) + 2 FROM dev.vGetRand), 1, 'U') /* 2. vagy 3. karakter nagybetű lesz*/
  SET @format = STUFF(@format, (SELECT (CAST(FLOOR(RandomNumber * 100) AS int) % 2) + 4 FROM dev.vGetRand), 1, 'D') /* 4. vagy 5. karakter számjegy lesz*/
  SET @format = STUFF(@format, (SELECT (CAST(FLOOR(RandomNumber * 100) AS int) % 2) + 6 FROM dev.vGetRand), 1, 'L') /* 6. vagy 7. karakter kisbetű  lesz*/

  WHILE @i <= @length BEGIN      
    SET @char = SUBSTRING (@format, @i, 1) 
    SET @return += 
      CASE @char 
        WHEN 'D' THEN SUBSTRING (@digit, (SELECT CAST(FLOOR(RandomNumber * (LEN(@digit) - 1) + 1) AS int) FROM dev.vGetRand), 1)
        WHEN 'U' THEN SUBSTRING (@upper, (SELECT CAST(FLOOR(RandomNumber * (LEN(@upper) - 1) + 1) AS int) FROM dev.vGetRand), 1)
        WHEN 'L' THEN SUBSTRING (@lower, (SELECT CAST(FLOOR(RandomNumber * (LEN(@lower) - 1) + 1) AS int) FROM dev.vGetRand), 1)
        WHEN 'C' THEN SUBSTRING (@character, (SELECT CAST(FLOOR(RandomNumber * (LEN(@character) - 1) + 1) AS int) FROM dev.vGetRand), 1)
      END
    SET @i += 1
  END
  RETURN @return  
END
GO