dbWriter SQL random number generator
One use for this code is to regenerate a new user ID's each time they log into
your database.
To execute the procedure use:
-- EXEC [dbo].zp_RAND 1,1,1,50, @OutPutVar OUT
CREATE procedure dbo.[ZP_Rand]
(
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@Rand_Length as smallint,
@Rand_OUT varchar(100) OUT
)
As
Begin
declare @characters varchar(100)
declare @count int
set @characters = ''
if @useNumbers = 1 ----------------------------------- load up
numbers 0 - 9
begin
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 ------------------------------- load up
uppercase letters A - Z
begin
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 ------------------------------- load up
lowercase letters a - z
begin
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 @Rand_OUT = ''
while @count <= @Rand_Length
begin
set @Rand_OUT = @Rand_OUT + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count)
as int)%LEN(@characters)+1,1)
set @count = @count + 1
end
end
GO |
|
