create function that return random number between 1000 and 10000 in sql

Question

i want create function that return random number between 1000 and 10000 in sql

and i create this

 create function dbo.RandomPass()
RETURNS int
 as
    begin
    DECLARE @RETURN int
    DECLARE @Upper INT;
    DECLARE @Lower INT;
    DECLARE @Random float;
    set @Random=RAND();

      SET @Lower = 1000 
      SET @Upper = 9999 
      set @RETURN= (ROUND(((@Upper - @Lower -1) * @Random + @Lower), 0))

 return @RETURN
 end;

but i get this error

Invalid use of a side-effecting operator 'rand' within a function.

Hiren Dhaduk · Accepted Answer · 2013-07-06 09:17:41Z

4

RAND() function is directly not allowed to use in the UDF so we have to find alternate way to use the same function. This can be achieved by creating a VIEW which is using RAND() function and use the same VIEW in the UDF.

Try following Query :

CREATE VIEW rndView
AS
SELECT RAND() rndResult
GO


create function dbo.RandomPass()
RETURNS int
 as
    begin
    DECLARE @RETURN int
    DECLARE @Upper INT;
    DECLARE @Lower INT;
    DECLARE @Random float;

    SELECT @Random = rndResult
    FROM rndView

      SET @Lower = 1000 
      SET @Upper = 9999 
      set @RETURN= (ROUND(((@Upper - @Lower -1) * @Random + @Lower), 0))

 return @RETURN
 end;

answered Jul 6, 2013 at 9:17

Hiren Dhaduk

2,7903 gold badges21 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sinan.petrus · Accepted Answer · 2015-01-02 15:10:22Z

2

To use a random number in user defined functions:

CREATE VIEW randomView
AS
SELECT RAND() randomResult
GO

The function will return a random number between two integers:

CREATE FUNCTION randomBetween(@lower INT, @upper INT)
RETURNS INT AS 
BEGIN
    DECLARE @random DECIMAL(18,18) 
    SELECT @random = randomResult FROM randomView

    DECLARE @randomInt INT = (CONVERT(INT, (@random*1000000)+@upper) % (@upper - @lower + 1)) + @lower

    RETURN @randomInt
END

Call the function:

DECLARE @lower INT = 1000
DECLARE @upper INT = 10000
SELECT dbo.randomBetween(@lower, @upper)

edited Jan 2, 2015 at 15:10

answered Jan 2, 2015 at 15:03

sinan.petrus

1413 silver badges5 bronze badges

Collectives™ on Stack Overflow

create function that return random number between 1000 and 10000 in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related