Random Password Generator Part II

As promised! A Part II of the Random Password Generator which was created as a stored procedure. I was stumped in the previous post as to how I was going to get this into a function. A function would be a lot nicer to use as you could sub-query it onto a table of users to generate new passwords (as an example).

While I was scratching my head, one of my colleges pointed out that you could put rand() into a view and call the view. Sceptical at first to the randomness of rand() in a view I dismissed it, but of course this was a viable solution!

So for your clipboard’s pleasure, here is the scalar function to randomly generate a password of variable length!
(use the buttons at the top of the code block to copy nicely!):

IF object_id('dbo.Rand_V') IS NOT NULL
	DROP VIEW dbo.Rand_V
GO

create View [dbo].[Rand_v]
as
	select RAND() as RandNum
GO

-- =============================================
-- Author:		Daniel Thompson
-- Create date: 30 May 2012
-- Description:	A Scalar function to return a random password!
-- ExampleA: SELECT dbo.GenerateRandomPassword(1,1,2,18)
-- ExampleB: SELECT username, dbo.GenerateRandomPassword(2,1,4,16) [NewPassword] From dbo.users
-- ExampleC: SELECT username, UPPER(dbo.GenerateRandomPassword(2,1,4,16)) [NewPassword] From dbo.users --all uppercase characters
-- =============================================
CREATE FUNCTION dbo.GenerateRandomPassword
(
	@qtyUpperCase	tinyint,
	@qtySymbols		tinyint,
	@qtyNumeric		tinyint,
	@passLength		int
)
RETURNS varchar(250)
AS
BEGIN

	-- Declare static character arrays.
	DECLARE @lowercase	varchar(26) = 'abcdefghijklmnopqrstuvwxyz',
			@uppercase	varchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
			@symbols	varchar(10) = '#@+?!£$^'

	DECLARE @qtyLowerCase	int	--Calculated

	SET @qtyLowerCase = @passLength - @qtyNumeric - @qtySymbols - @qtyUpperCase
	IF (@qtyLowerCase < 1)
	BEGIN
		--RAISERROR('Cannot generate password. Check length variables',16,1)
		RETURN '';
	END

	DECLARE @tblPassword TABLE(i float NOT NULL PRIMARY KEY CLUSTERED ,
								 c varchar(10) NOT NULL)
	DECLARE @r tinyint = 0

	--lowercase
	WHILE ((SELECT count(*) FROM @tblPassword) < @qtyLowerCase)
	BEGIN
		SET @r = ROUND((len(@lowercase)-1)*(SELECT TOP 1 RandNum FROM dbo.Rand_v) ,0)+1

		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v),substring(@lowercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols))
	BEGIN
		SET @r = ROUND((len(@symbols)-1)*(SELECT TOP 1 RandNum FROM dbo.Rand_v),0)+1

		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v), substring(@symbols,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase))
	BEGIN
		SET @r = ROUND((len(@uppercase)-1)*(SELECT TOP 1 RandNum FROM dbo.Rand_v),0)+1

		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v), substring(@uppercase,@r,1)
	END

	WHILE ((SELECT count(*) FROM @tblPassword) < (@qtyLowerCase + @qtySymbols + @qtyUpperCase + @qtyNumeric))
	BEGIN
		INSERT INTO @tblPassword(i,c)
		SELECT (SELECT TOP 1 RandNum FROM dbo.Rand_v), CAST(ROUND(9*(SELECT TOP 1 RandNum FROM dbo.Rand_v),0) AS int)
	END

	DECLARE @pass as varchar(250) = ''

	SELECT @pass = @pass + p1.c
	FROM @tblPassword p1
	ORDER BY i

	RETURN @pass

END
GO

This allows you to perform the following very easily:

SELECT
	 username
	,dbo.GenerateRandomPassword(2,1,4,16) [NewPassword]
FROM dbo.users

or...

UPDATE dbo.users
SET [Password]=dbo.GenerateRandomPassword(2,1,4,16)
WHERE [Password] IS NULL