HOWTO: Generate a Random String of Characters in SQL

Here's a stored procedure to generate a string of random characters. This is useful when, for example, one needs to generate a random password. Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.

Thanks to Raymond Lewallen over at CodeBetter.com

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Raymond Lewallen
--                    http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/17/63281.aspx
-- Create date: 2005/05/16
-- Description: Generate a random string of
--                            given length.
--                        : Everything is self-explanatory.
--                        : Right now max length is set to
--                            100. So anything between 1 and
--                            100 will work for a length.
--                        : If you specify a
--                            @charactersToUse, the bit flags
--                            get ignored.
--                        : All spaces are stripped from the
--                            @charactersToUse.
--                        : Characters can repeat. Will be
--                            handled in a future version.
--
-- Modifications:
-- Author:            A. Jackson
-- Create date: 2010/04/21
-- Description: Renamed the sp to fit into the
--                            naming convention of the
--                            database.
--                        : Formatted the header.
--                        : Reordered the parameters so that
--                            the @toReturn OUT parameter is
--                            first.
--                        : Added @useSpecialCharacters BIT.
--                        : Formatted the code.
--                        : Added default parameter values.
-- =============================================
CREATE PROCEDURE [dbo].[spGenerateRandomString]
        (
            @toReturn VARCHAR(100) OUT,
            @useSpecialCharacters BIT = 1,
            @useNumbers BIT = 1,
            @useLowerCase BIT = 1,
            @useUpperCase BIT = 1,
            @charactersToUse VARCHAR(100) = '',
            @stringLength INT = 100
        )
AS
BEGIN
        IF @stringLength <= 0  
        BEGIN
                RAISERROR ( 'Cannot generate a random string of zero length.', 16, 1 )
        END

        DECLARE @characters VARCHAR(110)
        DECLARE @count INT

        SET @characters = ''

        IF @useSpecialCharacters = 1
        BEGIN
        SET @count = 33
        WHILE @count <= 38
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 40
        WHILE @count <= 47
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 58
        WHILE @count <= 64
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 91
        WHILE @count <= 95
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 123
        WHILE @count <= 126
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
    END
    
        IF @useNumbers = 1  
        BEGIN
                -- load up numbers 0 - 9
                SET @count = 48
                WHILE @count <= 57
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END
        
        IF @useLowerCase = 1  
        BEGIN
        -- load up uppercase letters A - Z
        SET @count = 65
        WHILE @count <= 90
        BEGIN
                SET @characters = @characters +
                        CAST(CHAR(@count) AS CHAR(1))
                SET @count = @count + 1
        END
        END

        IF @useUpperCase = 1  
        BEGIN
                -- load up lowercase letters a - z
                SET @count = 97
                WHILE @count <= 122
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END

        SET @count = 0
        SET @toReturn = ''

        -- If you specify a character set to use, the bit flags get ignored.
        IF LEN(@charactersToUse) > 0  
        BEGIN
                WHILE CHARINDEX(@charactersToUse, ' ') > 0
                BEGIN
                        SET @charactersToUse = REPLACE(@charactersToUse, ' ', '')
                END

                IF LEN(@charactersToUse) = 0  
                BEGIN
                        RAISERROR ( 'Cannot use an empty character set.', 16, 1 )
                END

                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@charactersToUse,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@charactersToUse) +
                                                                                                    1, 1)
                        SET @count = @count + 1
                END
        END
        ELSE  
        BEGIN
                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@characters,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@characters) + 1,
                                                                                                    1)
                        SET @count = @count + 1
                END
        END
END

GO