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 AS BEGIN SET NOCOUNT ON DECLARE @counter tinyint DECLARE @nextChar char(1) SET @counter = 1 SET @randomString = ''

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

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

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.