gistfile1.sql

/*
Return a random TINYINT ( 0 - 255 )
usage : SELECT dbo.getRandomTinyint()

NOTE : This function is dependent on the VIEW
since you can't call RAND inside of a UDF()

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

*/


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

CREATE FUNCTION getRandomTinyint()
RETURNS tinyint
WITH EXECUTE AS CALLER
AS
BEGIN
-------------------------------------------------------------------------------
	DECLARE @max TINYINT, 
			@min TINYINT, 
			@rand NUMERIC( 18,4 ),
			@out TINYINT;

	-- set TINYINT parameters
	SET @max = 255;
	SET @min = 0;
	-- get RAND() from VIEW since we can't use it in UDF
	SELECT @rand = number FROM vRand

	-- make the number
	SELECT @out = CAST(((@max + 1) - @min ) * @rand + @min AS TINYINT);

	-- pass it back
	RETURN @out;
-------------------------------------------------------------------------------
END;
GO