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.