getRandomSmallint.sql

/******************************************************************************
Generate a random smallint
-------------------------------------------------------------------------------
USAGE :
	-- Get random smallint in the default range -32,768 - 32,767
	SELECT dbo.getRandomSmallint( NULL, NULL )

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

	-- Get random tinyint between 1000 and MAX tinyint
	SELECT dbo.getRandomSmallint( 1000, NULL )

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'getRandomSmallint') IS NOT NULL
   DROP FUNCTION getRandomSmallint
GO

CREATE FUNCTION getRandomSmallint( @min_in smallint, @max_in smallint )
RETURNS smallint
WITH EXECUTE AS CALLER
AS
BEGIN
-------------------------------------------------------------------------------
	DECLARE @max smallint, 
			@min smallint, 
			@rand NUMERIC( 18,10 ),
			@max_big NUMERIC( 38, 0 ),
			@rand_num NUMERIC( 38, 0 ),
			@out smallint;
 
	-- define this datatype's natural range
	SET @min = -32768	-- -32,768
	SET @max = 32767	-- 32,767

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

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