Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Call GETDATE Function in a User-Defined Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

One question I saw in one of the SQL Server forums I usually visit is how to generate a random password using a user-defined function.  Here’s my version of the random password generator user-defined function.

Using the trick in the tip of the day entitled, “How to Call NEWID() Function in a User-Defined Function”, I was able to come up with the following user-defined function that generates a random password.

As discussed on the said tip of the day, I will need to call the NEWID() function within the user-defined function.  Since I cannot call the NEWID() function directly from the user-defined function, I need to create a view first:

CREATE VIEW [dbo].[RandomNewID]
AS
SELECT NEWID() AS [NewID]
GO

Now that I have this view, I can now create my user-defined function that will generate a random password.  To make the function more flexible, it accepts the number of characters that the user wants for the password:

CREATE FUNCTION [dbo].[ufn_GeneratePassword] ( @PasswordLength INT )
RETURNS VARCHAR(20)
AS
BEGIN

DECLARE @Password     VARCHAR(20)
DECLARE @ValidCharacters   VARCHAR(100)
DECLARE @PasswordIndex    INT
DECLARE @CharacterLocation   INT

SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890'

SET @PasswordIndex = 1
SET @Password = ''

WHILE @PasswordIndex <= @PasswordLength
BEGIN
 SELECT @CharacterLocation = ABS(CAST(CAST([NewID] AS VARBINARY) AS INT)) %
LEN(@ValidCharacters) + 1
 FROM [dbo].[RandomNewID]

 SET @Password = @Password + SUBSTRING(@ValidCharacters, @CharacterLocation, 1)

 SET @PasswordIndex = @PasswordIndex + 1
END

RETURN @Password

END

Here are some sample output using the generate password function:

SELECT [dbo].[ufn_GeneratePassword] ( 8 ) AS [Password8],
       [dbo].[ufn_GeneratePassword] ( 10 ) AS [Password10],
       [dbo].[ufn_GeneratePassword] ( 12 ) AS [Password12]

Password8 Password10 Password12
--------- ----------- ------------- rj58PffA 0x8dwB9peF iuLysW0zA2jy

Back to Tip of the Day List Next Tip