getRandomInt.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