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
Improving my SQL BI Skills 
ALTER PROCEDURE [dbo].[aesp_GenerateRandomString] (
@useNumbersOnly bit = 1,
@useAlphabetOnly bit = 1,
@useLowerCase bit = 1,
@charactersToUse as nvarchar(100) = ”,
@passwordLength as smallint = 10,
@password varchar(30) OUTPUT
)
AS
BEGIN
DECLARE @characters nvarchar(100) = ”;
DECLARE @count int = 0;
SET @password = ”;
IF @passwordLength <= 0
RAISERROR('Cannot generate a random string of zero password length.',16,1)
IF @useNumbersOnly = 1 AND @useAlphabetOnly = 1
RAISERROR('Choose one of options: @useNumbersOnly OR @useAlphabetOnly.',16,1)
IF @useNumbersOnly = 1
BEGIN
— load up numbers 0 – 9
SET @charactersToUse = '0123456789';
END
ELSE IF @useAlphabetOnly = 1
BEGIN
— load up alphabet
SET @charactersToUse = 'abcdefghijklmnoprstuvwxyz';
END
ELSE
BEGIN
SET @charactersToUse = ISNULL(replace(@charactersToUse,' ',''),'');
IF LEN(@charactersToUse) = 0
BEGIN
SET @charactersToUse = 'abcdefghijklmnoprstuvwxyz';
END
END –end of use numbers
declare @Upper int = LEN(@charactersToUse);
declare @Lower int = 1;
while @count < @passwordLength
begin
declare @indexer int = ROUND(((@Upper – @Lower) * RAND() + @Lower), 0);
set @password = @password + SUBSTRING(@charactersToUse, @indexer, 1);
set @count = @count + 1
end
— upper of lower if not numbers
if @useNumbersOnly = 0
BEGIN
if @useLowerCase = 1
BEGIN
SET @password = LOWER(@password);
END
ELSE
BEGIN
SET @password = UPPER(@password);
END
END
END
LikeLiked by 1 person
even better:
USE [playground]
GO
/****** Object: StoredProcedure [dbo].[aesp_GenerateRandomString] Script Date: 31/05/2019 07:56:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[aesp_GenerateRandomString] (
@useNumbersOnly bit = 1,
@useLowerCase bit = 1,
@charactersToUse as nvarchar(100) = ‘abcdefghijklmnoprstuvwxyz’,
@passwordLength as smallint = 10,
@password varchar(30) OUTPUT
)
AS
BEGIN
DECLARE @characters nvarchar(100) = ”;
DECLARE @count int = 0;
SET @password = ”;
IF @passwordLength <= 0
RAISERROR('Cannot generate a random string of zero password length.',16,1)
IF @useNumbersOnly = 1
BEGIN
— load up numbers 0 – 9
SET @charactersToUse = '0123456789';
END
ELSE
BEGIN
SET @charactersToUse = ISNULL(replace(@charactersToUse,' ',''),'');
IF LEN(@charactersToUse) = 0
BEGIN
SET @charactersToUse = 'abcdefghijklmnoprstuvwxyz';
END
IF @useLowerCase = 1
BEGIN
SET @charactersToUse = LOWER(@charactersToUse);
END
ELSE
BEGIN
SET @charactersToUse = UPPER(@charactersToUse);
END
END –end of use numbers
declare @Upper int = LEN(@charactersToUse);
declare @Lower int = 1;
while @count < @passwordLength
begin
declare @indexer int = ROUND(((@Upper – @Lower) * RAND() + @Lower), 0);
set @password = @password + SUBSTRING(@charactersToUse, @indexer, 1);
set @count = @count + 1
end
END
LikeLiked by 1 person