在用户定义函数中使用 RAND() [英] Use RAND() in User Defined Function

问题描述

我正在尝试创建一个用户定义的函数，该函数调用其中的系统 RAND() 函数，当我尝试创建该函数时，它会出错并显示以下消息:

I am trying to create a user defined function which calls the system RAND() function inside it, when I try to create the function it errors out with the following message:

消息 443，级别 16，状态 1，过程 getNumber，第 10 行
在函数中无效使用副作用运算符rand".

Msg 443, Level 16, State 1, Procedure getNumber, Line 10
Invalid use of a side-effecting operator 'rand' within a function.

我的功能代码:

CREATE FUNCTION getNumber(@_id int)
RETURNS DECIMAL(18,4)
AS
BEGIN
   DECLARE @RtnValue DECIMAL(18,4);

   SELECT TOP 1 @RtnValue = EmployeeID 
   FROM dbo.Employees
   ORDER BY EmployeeID DESC

   SET @RtnValue = RAND() * @RtnValue * (1/100)

   RETURN @RtnValue;
END

我该如何解决这个问题?

How can I fix this please?

推荐答案

问题是您不能从用户定义的函数内部调用非确定性函数.

The problem is that you cannot call a non-deterministic function from inside a user-defined function.

我通过创建视图、在视图内调用该函数并在您的函数内使用该视图来解决此限制，就像这样......

I got around this limitation by creating a view, call that function inside the view and use that view inside your function, something like this......

CREATE VIEW vw_getRANDValue
AS
SELECT RAND() AS Value

功能定义

ALTER FUNCTION getNumber(@_id int )
RETURNS DECIMAL(18,4)
AS
BEGIN
   DECLARE @RtnValue DECIMAL(18,4);
   SELECT TOP 1 @RtnValue = EmployeeID 
   FROM dbo.Employees
   ORDER BY EmployeeID DESC

   SET @RtnValue = (SELECT Value FROM vw_getRANDValue) * @RtnValue * (1.0000/100.0000) --<-- to make sure its not converted to int
    RETURN @RtnValue;
END

这篇关于在用户定义函数中使用 RAND()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！