SQL Tips #29 - A Function to Generate Random Numbers

A reusable function to generate random integers within a range, using a view-based NEWID() workaround (functions can't call NEWID() directly).

The Query

SELECT NEWID() AS MyNewID

Then define the function:

CREATE FUNCTION random_range 
(
	@rangeStart int,
	@rangeEnd int
)
RETURNS int
AS
BEGIN
	DECLARE @rangeSize int = (@rangeEnd - @rangeStart) + 1

	DECLARE @result int = (@rangeStart + (ABS(CHECKSUM((SELECT top 1 MyNewID FROM Get_NewID)) % @rangeSize))

	-- Return the result of the function
	RETURN @result

END
GO

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags