RANDBETWEEN for SQL Server 2012
What about
- Use
RAND()
(which returns a value between 0 and 1 (exception). - multiply by 298 (since you want dynamic range [300-3] = 297 + 1)
- add 3 to offset
- and discarded to INT?
i.e.
SELECT CAST(RAND() * 298 + 3 AS INT)
( Edit Also see @ivo's answer on how to turn this into a custom function)
source to share
Based on StuartLC solution, you can also use Stored Procedure Like this if you want to use 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;
And Call it like this:
DECLARE @tmp INT;
EXECUTE [dbo].[RANDBETWEEN] 0,10, @ret=@tmp OUT ;
SELECT @tmp
To create a function, you must first create a view:
CREATE VIEW Get_RAND
AS
SELECT RAND() AS MyRAND
GO
Then you can create a function like this (accessing the view with SELECT MyRand ...):
CREATE FUNCTION RANDBETWEEN(@LowerBound INT, @UpperBound INT)
RETURNS INT
AS
BEGIN
DECLARE @TMP FLOAT;
SELECT @TMP = (SELECT MyRAND FROM Get_RAND);
RETURN CAST(@TMP* (@UpperBound - @LowerBound) + @LowerBound AS INT);
END
GO
Finally, you can call your function like this:
SELECT [dbo].[RANDBETWEEN](1,10)
source to share
The range rand()
on SQL Server is [0, 1].
If you want to match this to an integer starting at A and ending at B:
-- option #1
declare @rnd int = @B + 1;
while @rnd = @B + 1
set @rnd = cast(rand() * (@B - @A + 1) + @A as int);
-- option #2
declare @rnd int;
set @rnd = cast(rand() * (@B - @A + 1) + @A as int);
if @rnd = @B + 1 set @rnd = @B;
Casting to int truncates, but there is still a very subtle possibility of an expression evaluating @B + 1, which you probably want to handle by creating a new number or randomly translating it to a different value.
If you want to map this to a row of reals starting with A and going to B:
rand() * (@B - @A) + @A
source to share
I believe you need to adjust the upper and lower bounds to .5 in either direction to have equal distribution. If you run an existing function between 1.4 for 4000 lines, you get about half the frequency of 1s and 4s that you get with 2 and 3.
So I suggest this repair:
return (select round((((@UPPER+.5)-(@LOWER-.5))*RAND + (@LOWER-.5)),0)
from vw_rand)
source to share