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