byrmol Blog

Calling non-deterministic functions is not allowed inside a UDF, which seems to preclude any sort of “random” number generation. Fortunately a UDF can be “tricked” into thinking it is deterministic by using views.

Below is a simple RandomNumber generator based on the GUID data type. It simply reverses the GUID and takes the RIGHT @MaxDigits characters and ensures they are actual numbers.

--this view simply returns a new GUID
CREATE VIEW vNEWID AS
SELECT NEWID() AS NEWID
GO
--A Function that uses part of the GUID for a random number
CREATE FUNCTION RandomNumber
( @MaxDigits INT = 4 )
RETURNS INT
AS
BEGIN
DECLARE @TextID VARCHAR(36)
SELECT @TextID = RIGHT(CAST(NEWID AS VARCHAR(36)),@MaxDigits) FROM vNewID
WHILE PATINDEX('%[^0-9]%', @TextID) > 0
BEGIN
SELECT @TextID = RIGHT(CAST(NEWID AS VARCHAR(36)),@MaxDigits)FROM vNewID
END
RETURN CAST(@TextID as INT)
END
GO
SELECT dbo.RandomNumber(DEFAULT)