Generate Random Alphanumeric Strings

CREATE PROC dbo.GenerateRandomAlphaNumericIDs 
(
@HowMany INT = 500
)

/*
Description: Generates random alphanumeric values
Usage:EXEC dbo.GenerateRandomAlphaNumericIDs or 
EXEC dbo.GenerateRandomAlphaNumericIDs 50
To do: Validation and ERROR checking 
*/
AS

DECLARE @Loop INT
DECLARE @NoOfRandomIDs INT
DECLARE @cnt TINYINT
DECLARE @MaxLen TINYINT --len of id
DECLARE @ret int
DECLARE @Code VARCHAR(8)
DECLARE @TblCount INT
DECLARE @TblRandID TABLE (RowID INT IDENTITY(1,1) NOT NULL, RandomID VARCHAR(8) NOT NULL)

SET NOCOUNT ON

SET @NoOfRandomIDs = @HowMany
SELECT @TblCount = COUNT(*) FROM @TblRandID
SET @Loop = 1

WHILE @Loop <= @NoOfRandomIDs AND @TblCount <= @NoOfRandomIDs
BEGIN --outer
SET @cnt=1
SET @MaxLen = 8
SET @Code = ''
WHILE @cnt<=@MaxLen
BEGIN --inner
SELECT @ret = CONVERT(INT, RAND() * POWER(2,8))
IF (@ret <58)
BEGIN
SET @Code=@Code + CASE 
WHEN LEN(@ret)=1 THEN CAST(@ret AS VARCHAR)
WHEN LEN(@ret)=2 THEN CAST(@ret % 10 AS VARCHAR)
WHEN LEN(@ret)=3 THEN CAST(@ret % 100 AS VARCHAR)
ELSE '9' --not really neccessary since max @ret is 256 i.e. power(2,8)
  END
END
ELSE IF @ret >= 58
BEGIN
SET @Code = @Code + CHAR((CONVERT(INT, RAND() * POWER(2,8)) % 26) + 65) --A=65
END
-- increment 
SET @cnt = @cnt+1
END --inner

-- verify that @code is not already in table
IF NOT EXISTS(SELECT * FROM @TblRandID WHERE RandomID = @Code)
BEGIN
INSERT INTO @TblRandID VALUES(@Code)
END
-- check no in table
SELECT @TblCount = COUNT(*) FROM @TblRandID
-- increment @Loop
SET @Loop = @Loop + 1
END --outer

-- Display results
SELECT * FROM @TblRandID

SET NOCOUNT OFF



GO