How do I generate random number with a range X times in SQL SERVER

I want to generate a random number X times with a range. I’ve tried to create a function:

CREATE FUNCTION Random_Number (@Times INT, @Upper BIGINT,@Lower BIGINT, @randomvalue numeric(18,10))
RETURNS INT
AS
BEGIN
    DECLARE @Random BIGINT
    DECLARE @Row INT

    SET @Row = 1

    WHILE  @Row <= @Times
    BEGIN
        SELECT @Random = ROUND(((@Upper - @Lower -1) * @randomvalue + @Lower), 0)
        SET @Row = @Row + 1
    END
    RETURN @Random
END
GO


select dbo.Random_Number(5,2002100001,2002100010,RAND())

The query only returns a single row:

#1 2002100003

I want the following result:

#1 2002100003
#2 2002100000
#3 2002100009
#4 2002100006
#5 2002100007

Is it possible?

Answer

You can use recursive approach :

with cte as (
     select 2002100001 as st, 2002100010 as ed
     union all
     select c.st + 1, c.ed
     from cte c
     where c.st < c.ed
)
select top (5) st
from cte
order by newid();

Advertisement

Answer