Random SQL Tricks (Part 2)

In my previous random SQL tricks post, I discussed how to generate random alphanumeric strings of any length.  A slight variation on that idea that also proved useful in generating test data is the following stored procedure (which generates a varchar consisting entirely of numbers):

CREATE PROCEDURE [dbo].[SpGenerateRandomNumberString]
@randomString varchar(15) OUTPUT
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”

WHILE @counter <= 15
SELECT @nextChar = CHAR(48 + CONVERT(INT, (57-48+1)*RAND()))

SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1

The range in the select for @nextChar maps to ASCII values for the digits 0-9.  Unlike the stored procedure from my first post, there’s no if statement to determine whether or not the random value retrieved is allowed because the ASCII range for digits is contiguous.  The needs of my application restricted the length of this numeric string to 15 characters.  For more general use, the first refactoring would probably add string length as a second parameter, so the numeric string could be a variable length.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.