## 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)
• 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)
```