RANDBETWEEN(m,n) in Sql Server?

Today I have to find random number between two numbers lots of the time in the stored procedures So I tried to create a function which return random numbers between two boundary numbers m and n. I tried to look for the solution and got below logic to implement the function.

Let m = 5 and n = 500 and method call would be RANDBETWEEN(5, 500). Then logic to find the random number would be as below:

  • Use RAND() (which returns a value between 0 and 1 (exclusive).
  • multiply by 298 (since you want a dynamic range of [300-3] = 297 + 1)
  • add 3 to Offset
  • and cast to INT?
Usage:
SELECT CAST(RAND() * 298 + 3 AS INT)
A Stored Procedure can be written like this if it supposed to be reuse in code more often:
CREATE PROCEDURE [dbo].[RANDBETWEEN]
@LowerBound int = 0 ,
@UpperBound int = 1 , 
@ret int OUT
AS
    BEGIN
    SET NOCOUNT ON;
    SELECT @ret = (CAST((RAND() * (@UpperBound - @LowerBound)) + @LowerBound AS INT));
RETURN ;
END;

Call can be made like this:

DECLARE @tmp INT;
EXECUTE [dbo].[RANDBETWEEN] 0,10, @ret=@tmp OUT ;
SELECT @tmp

To create a function I found that I have to create a View that make random method call and then function will do remaining operation to get the random number.

CREATE VIEW Get_RAND
AS
SELECT RAND() AS RANDNumber
GO



Then you can create a function like this (accessing the view with the SELECT RandomNumber... ) :
CREATE FUNCTION RANDBETWEEN(@LowerBound INT, @UpperBound INT)
RETURNS INT
AS
BEGIN
    DECLARE @TMP FLOAT;
    SELECT @TMP = (SELECT RandomNumber FROM Get_RAND);
    RETURN CAST(@TMP* (@UpperBound - @LowerBound) + @LowerBound AS INT);
END
GO

Then this function can be called as below:

SELECT [dbo].[RANDBETWEEN](1,10)