If you have ever tried to create a random string function it can be a little tricky. It is very easy to do this in sql or in a stored procedure however it is not possible to call the function RAND() from inside a function on mssql server.
An example of this is below ..

CREATE FUNCTION TestRand()
RETURNS int
BEGIN
        DECLARE @ret int
        SELECT @ret = CONVERT(int, RAND() * 100)
        RETURN @ret
END

If you attempt to run the code above you are just going to get the following error.

 Invalid use of a side-effecting operator 'rand' within a function.

There is actually a very easy way to work around this problem by simply moving the RAND function outside of the function. A really easy way to do this is to create a view for it. Like this

CREATE VIEW [dbo].[Random]
        AS SELECT RAND() AS RAND
GO

So by doing this we can avoid the error above.

CREATE FUNCTION TestRand()
RETURNS int
BEGIN
        DECLARE @ret int
        SELECT @ret = CONVERT(int, RAND*100) FROM Random
        RETURN @ret
END
GO

SELECT dbo.TestRand()

So now that we have an easy way to generate random data inside a function. We can work with this to generate random strings of data. Using the following function.

CREATE FUNCTION RandomString (@str AS varchar(MAX), @len AS int)
RETURNS varchar(MAX)
BEGIN
        DECLARE @newstr VARCHAR(MAX)
        DECLARE @counter int

        SET @newstr = ''
        SET @counter = 0

        WHILE @counter < @len
        BEGIN

                SELECT @newstr = @newstr + SUBSTRING(@str, (SELECT CONVERT(int, (RAND * LEN(@str) + 1)) FROM Random), 1)
                SET @counter = @counter + 1
        END

        RETURN @newstr
END
GO

I am sure you are wondering what is the point in this. Well that is simple if you have ever attempted to generate specific test data in a stored procedure you end up doing a few loops a lot of function calls and repeating a lot of code. Now that the functions above are working using a tally table which is just a table with increment numbers. Using this can generate as much data as we will ever need! Here is an example ..

INSERT INTO Customer (FirstName, LastName, EMail)
        SELECT TOP 1000
                dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 25) AS FirstName,
                dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 25) AS LastName,
                dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 6) + '@' + dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 5) + '.local' AS EMail
                        FROM Tally




Last Modified: 23 February 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-29 - MSSQL - TRIM Function