sql-server - 在用户定义的函数中检索随机行？

Question

我正在尝试定义这个函数:

CREATE FUNCTION getRandomName  ()
    RETURNS VARCHAR(48)
AS BEGIN
    -- concatenate two random strings from two columns in a table and return as a new string
    DECLARE @finalStr VARCHAR(48);
    SET @finalStr = (SELECT TOP 1 st1 FROM randomStrings ORDER BY RAND()) + 
        ' ' + 
        (SELECT TOP 1 st2 FROM randomStrings ORDER BY RAND());
    RETURN @finalStr;
END

我不能这样做，因为:

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

我在网上找到的与此问题相关的帖子建议在调用函数时传入一个随机值作为参数，或者使用 View 并在函数中查询该 View 以将单个随机数放入变量中。我不能使用这些方法，因为我试图在 ORDER BY 子句中使用随机化。

有没有办法做到这一点？

(SQL 服务器 2014)

---

编辑:

因此您可以使用 View 来获得如下所述的结果，但现在我发现自己需要将参数传递给函数:

CREATE FUNCTION getRandomName  (
    @maxPieceSize int
)
    RETURNS VARCHAR(48)
AS BEGIN
    -- concatenate two random strings from two columns in a table and return as a new string
    DECLARE @finalStr VARCHAR(48);
    SET @finalStr = (SELECT TOP 1 st1 FROM randomStrings WHERE LEN(st1) <= @maxPieceSize ORDER BY RAND()) + 
        ' ' + 
        (SELECT TOP 1 st2 FROM randomStrings WHERE LEN(st1) <= @maxPieceSize ORDER BY RAND());
    RETURN @finalStr;
END

所以我无法为这种情况创建 View ，因为您无法将参数传递给 View 。

所以这是我的困境:

• 函数:我不能使用它，因为我不能在函数内使用任何不确定的函数。
• View :我不能使用它，因为我需要将参数传递给“函数”。
• 过程:我能看到的唯一方法是使用输出变量，这意味着声明一个变量等。我无法简单地执行类似EXECUTE getRandomName(6) 或 SELECT getRandomName(6)。

我是否坚持使用一个过程并以“困难的方式”执行它(使用输出变量，并且每次我想使用该方法时都必须声明该变量)？

---

再次编辑:

我尝试将实际方法编写为存储过程，然后从声明变量的函数调用该存储过程，分配它然后返回它。这是有道理的。除了....

Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.

我猜 SQL Server 真的不希望我有一个可以返回随机值的函数。 (有趣，因为 RAND() 本身不是一个函数吗？)

Answer 1

为什么你坚持使用函数？将 View 用作函数:

CREATE view getRandomName
AS 
    SELECT (SELECT TOP 1 st1 FROM randomStrings ORDER BY Newid()) + 
        ' ' + 
        (SELECT TOP 1 st1 FROM randomStrings ORDER BY Newid())
as RandomName
GO
SELECT (SELECT RandomName FROM getRandomName) + ' - This is random name'
GO

还有一种古老而疯狂的方法可以在存储过程中获取随机行:

CREATE PROCEDURE usp_Random_Message
@i INT
AS
SELECT TOP 1 * FROM (
    SELECT TOP (@i) * FROM sys.Messages
    ORDER BY message_id
) AS a ORDER BY message_id DESC
GO
DECLARE @i INT = CAST(RAND() * 100 as INT);
EXEC usp_Random_Message @i;