Generate Random Strings

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Rafal Skotak

Date: 14 Jan 2008

Description

Generates a list of random varchar(8) strings.

Currently only includes alphabetic characters, but could be extended to include all aphanumeric characters, which may make it usable as a random password generator.

Otherwise, it could be used to generate test data.

Code

/************************************************************************

*

* Author Rafal Skotak

* Purpose Procedure generates a list of random varchars

* Date 2008-01-14

*

************************************************************************/

IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.proc_random_varchars') AND xtype = 'P')

DROP PROCEDURE dbo.proc_random_varchars

GO

CREATE PROCEDURE dbo.proc_random_varchars

@count INT = NULL

AS

BEGIN

SET NOCOUNT ON

----------------------------------------------------------

-- check parameters

IF @count IS NULL

SET @count = 1024

IF @count < 1 OR @count > 999999

BEGIN

RAISERROR('Invalid @count value', 16, 1)

RETURN

END

----------------------------------------------------------

--

CREATE TABLE #temp_result_table

(

rec_id INT IDENTITY(1, 1) PRIMARY KEY,

value VARCHAR(8) NOT NULL

)

WHILE @count > 0

BEGIN

INSERT INTO #temp_result_table (value) VALUES

(

CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +

CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A')))

);

SET @count = @count - 1

END

SELECT * FROM #temp_result_table

DROP TABLE #temp_result_table

END

GO

--- example:

EXEC dbo.proc_random_varchars 128