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