Technical Article

Random varchars

,

This procedure generates a list of random varchars (8 charactes length, only letters - lower or upper case).

/************************************************************************
*
* 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

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating