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'))) +
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'))) +
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'))) +
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
|
