Generate random integers using tsql UDFs

Ever need to generate random numbers from the integer family?  I had this need on a project so I whipped up these four tsql User Defined Functions to help with this task.  There are four functions in all, one for tinyint, smallint, int, and bigint.  Additionally, you will need to create one VIEW since you can not fire the tsql function RAND() inside of a udf.

With these functions, you can generate random integers in their native range.

 SELECT dbo.getRandomInt( NULL, NULL ) 

Or you can restrict your random integers to a range of your liking.

 SELECT dbo.getRandomInt( 1000, 1000000000) 

Just as a reminder, here are the native ranges for these four integer types as supported by MS SQL Server 2005

Each of these functions have the same structure and primarily differ only by the integer type’s native range.  Here is the guts of one of the UDFs in case you want just the facts.

/******************************************************************************
Generate a random int
-------------------------------------------------------------------------------
USAGE :
 -- Get random int in the default range -2,147,483,648 to 2,147,483,647
 SELECT dbo.getRandomInt( NULL, NULL )

 -- Get random tinyint within a specific range
 SELECT dbo.getRandomInt( 1000, 30000 )

REQUIREMENT : Since you can't call RAND() inside of a UDF,
this function is dependant on the following VIEW vRand :

-- BEGIN VIEW
 -- This is only a helper VIEW since currently you can not use RAND() in a UDF
 -- DROP VIEW vRand
 CREATE VIEW [dbo].[vRand]
 AS
 SELECT RAND() AS 'number'
-- END VIEW

******************************************************************************/

USE SmartEarth
GO

IF OBJECT_ID (N'getRandomInt') IS NOT NULL
 DROP FUNCTION getRandomInt
GO

CREATE FUNCTION getRandomInt( @min_in int, @max_in int )
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
-------------------------------------------------------------------------------
 DECLARE @max int,
 @min int,
 @rand NUMERIC( 18,10 ),
 @max_big NUMERIC( 38, 0 ),
 @rand_num NUMERIC( 38, 0 ),
 @out int;

 -- define this datatype's natural range
 SET @min = -2147483648    -- -2,147,483,648
 SET @max = 2147483647    -- 2,147,483,647

 -- Check to see if a range has been passed in.
 -- Otherwise, set to default tinyint range
 IF( @min_in is not null AND @min_in > @min )
 SET @min = @min_in

 IF( @max_in is not null AND @max_in < @max )
 SET @max = @max_in
 -- end range check

 -- get RAND() from VIEW since we can't use it in UDF
 SELECT @rand = number FROM vRand

 -- CAST @max so the number generation doesn't overflow
 SET @max_big = CAST( @max AS NUMERIC(38,0) )

 -- make the number
 SELECT @rand_num = ( (@max_big + 1) - @min ) * @rand + @min;

 -- validate rand
 IF( @rand_num > @max )
 -- too big
 SET @out = @max
 ELSE IF ( @rand_num < @min )
 -- too small
 SET @out = @min
 ELSE
 -- just right, CAST it
 SET @out = CAST( @rand_num AS int )

 -- debug
 -- SELECT @min_in AS 'min_in', @max_in AS 'max_in', @min AS 'min', @max AS 'max', @rand, @rand_num AS 'rand_num', @out AS 'out'

 -- return appropriate
 RETURN @out;

-------------------------------------------------------------------------------

END;
GO

So where do you get the code?

You can view all functions and view online at the following gist.github urls:

• VIEW : vRand
• UDF : getRandomTinyint
• UDF : getRandomSmallint
• UDF : getRandomInt
• UDF : getRandomBigint

Or you can just download all the source code in one zip file here.

Hopefully this will help somebody out.  If you’re a DBA or just a tsql wizard, let me know what you think.  Can I do these functions a better way?  Is this already built into SQL2005 and I just didn’t know it?  All of this tsql was written against SQL Server 2005, but I’m pretty sure it would work on SQL2000 and SQL2008 as well.