DECLARE @min INT = 3;
DECLARE @max INT = 6;
SELECT @min + ROUND(RAND() * (@max - @min), 0);
Step by step
DECLARE @min INT = 3;
DECLARE @max INT = 6;
DECLARE @rand DECIMAL(19,4) = RAND();
DECLARE @difference INT = @max - @min;
DECLARE @chunk INT = ROUND(@rand * @difference, 0);
DECLARE @result INT = @min + @chunk;
SELECT @result;
Note that a user-defined function thus not allow the use of RAND(). A workaround for this (source: http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/) is to create a view first.
CREATE VIEW [dbo].[vw_RandomSeed]
AS
SELECT RAND() AS seed
and then create the random function
CREATE FUNCTION udf_RandomNumberBetween
(
@min INT,
@max INT
)
RETURNS INT
AS
BEGIN
RETURN @min + ROUND((SELECT TOP 1 seed FROM vw_RandomSeed) * (@max - @min), 0);
END