我想要创建一个函数,在sql中返回1000到10000之间的随机数
我创建了这个
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;
但是我收到这个错误
Invalid use of a side-effecting operator 'rand' within a function.
RAND()函数直接不允许在UDF中使用,因此我们必须找到使用相同函数的替代方法。这可以通过创建一个使用RAND()函数的VIEW并在UDF中使用相同的VIEW来实现。
尝试以下查询:
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;
在用户定义的函数中使用随机数:
CREATE VIEW randomView
AS
SELECT RAND() randomResult
GO
函数将返回两个整数之间的随机数:
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
调用函数:
DECLARE @lower INT = 1000
DECLARE @upper INT = 10000
SELECT dbo.randomBetween(@lower, @upper)