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.

Answer 1

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;