getRandomBigint.sql

/******************************************************************************
Generate a random bigint
-------------------------------------------------------------------------------
USAGE :
	-- Get random bigint in the default range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
	SELECT dbo.getRandomBigint( NULL, NULL )

	-- Get random tinybigint within a specific range
	SELECT dbo.getRandomBigint( 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'getRandomBigint') IS NOT NULL
   DROP FUNCTION getRandomBigint
GO

CREATE FUNCTION getRandomBigint( @min_in bigint, @max_in bigint )
RETURNS bigint
WITH EXECUTE AS CALLER
AS
BEGIN
-------------------------------------------------------------------------------
	DECLARE @max bigint, 
			@min bigint, 
			@rand NUMERIC( 18,10 ),
			@max_big NUMERIC( 38, 0 ),
			@rand_num NUMERIC( 38, 0 ),
			@out bigint;
 
	-- define this datatype's natural range
	SET @min = -9223372036854775808	-- -9,223,372,036,854,775,808
	SET @max = 9223372036854775807	-- 9,223,372,036,854,775,807

	-- Check to see if a range has been passed in.
	-- Otherwise, set to default tinybigint 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 bigint )

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