GENERATE RANDOM STRING IN SQL SERVER 2005
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[aesp_GenerateRandomString] (
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@charactersToUse as varchar(100),
@passwordLength as smallint,
@password varchar(30) OUTPUT
)
AS
BEGIN
DECLARE @characters varchar(100)
DECLARE @count int
IF @passwordLength <= 0
RaisError(‘Cannot generate a random string of zero length.’,16,1)
SET @characters = ”
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 @password = ”
— 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
RaisError(‘Cannot use an empty character SET.’,16,1)
WHILE @count <= @passwordLength
BEGIN
SET @password = @password +
SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as
int)%LEN(@charactersToUse)+1,1)
SET @count = @count + 1
END
END
ELSE
BEGIN
WHILE @count <= @passwordLength
BEGIN
SET @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
SET @count = @count + 1
END
END
SELECT @password
END
_________________________________________________
DECLARE @OutString VARCHAR(20)
exec dbo.aesp_GenerateRandomString 1,1,1,null,8,@OutString OUT
PRINT ‘Hi- ‘ + @OutString
______________________________________
The above stored procedure will give you a complex random sting every time.
Happy programming.Keep learning.
Pawan Kumar
Pawankkmr@hotmail.com