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.

[sql] SELECT dbo.getRandomInt( NULL, NULL ) [/sql]

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

[sql] SELECT dbo.getRandomInt( 1000, 1000000000) [/sql]

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

bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint	-2^15 (-32,768) to 2^15-1 (32,767)
tinyint	0 to 255

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.

[sql]/******************************************************************************
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[/sql]

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.