One of my most recent tasks at work has been generating test data for integration tests of a new application. We don’t have the version of Visual Studio which does it for you, and rather than write an app that did it, I spent the past week hunting for examples that just used Transact-SQL. The initial post that I found the most useful is this one, in which the author provides five different ways of generating random numbers. I use his third method quite often, as you’ll see in this post (and any others I write on this topic).
One of our needs for random test data was alphanumeric strings of varying lengths. Because the content of the text mattered less than the need for text, it didn’t have to resemble actual names (or anything recognizable). The first example I found of a T-SQL stored procedure for generating a random string was in this blog post by XSQL Software. The script does generate random strings, but they include non-alphanumeric characters. To get the sort of random strings I wanted, I took the random number generation method from the first post and the stored procedure mentioned earlier and adapted them to this:
CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”
WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))
IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END
The range in the select for @nextChar is the set of ASCII table values that map to digits, upper-case letters, and lower-case letters (among other things). The “if” branch values in the set are those ASCII table values that map to punctuation, brackets, and other non-alphanumeric characters. Only alphanumeric characters are added to @randomString as a result. Having a stored procedure like this one available makes it much easier to generate test data, especially since it can be called from other stored procedures.