如何在 SQL Server 中创建一个接受数据列的函数？

Question

本周早些时候，我在 SQL Server 2008 中创建了以下函数，该函数采用两个参数，并使用它们来选择一列“详细”记录，并将它们作为逗号分隔值的单个 varchar 列表返回。现在我开始考虑这个问题，我想采用这个表和特定于应用程序的函数并使其更加通用。

我不太熟悉定义 SQL 函数，因为这是我的第一次。如何更改此函数以接受单个“列”的数据，以便我可以以更通用的方式使用它？

而不是调用：

SELECT ejc_concatFormDetails(formuid, categoryName)

我想让它像这样工作：

SELECT concatColumnValues(SELECT someColumn FROM SomeTable)

这是我的函数定义：

FUNCTION [DNet].[ejc_concatFormDetails](@formuid AS int, @category as VARCHAR(75))
RETURNS VARCHAR(1000) AS
BEGIN
 DECLARE @returnData VARCHAR(1000)
 DECLARE @currentData VARCHAR(75)
 DECLARE dataCursor CURSOR FAST_FORWARD FOR
  SELECT data FROM DNet.ejc_FormDetails WHERE formuid = @formuid AND category = @category

 SET @returnData = ''

 OPEN dataCursor

 FETCH NEXT FROM dataCursor INTO @currentData
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
  SET @returnData = @returnData + ', ' + @currentData
  FETCH NEXT FROM dataCursor INTO @currentData
 END

 CLOSE dataCursor
 DEALLOCATE dataCursor

 RETURN SUBSTRING(@returnData,3,1000)
END

如您所见，我在函数中选择列数据，然后用光标循环结果以构建逗号分隔的 varchar。

如何更改它以接受作为结果集的单个参数，然后使用游标访问该结果集？

Answer 1

其他人已经回答了您的主要问题 - 但让我指出您的函数的另一个问题 - 游标的糟糕使用！

您可以轻松重写此函数以不使用游标、不使用 WHILE 循环 - 与此无关。它会更快，也更容易 - 代码更少：

FUNCTION DNet.ejc_concatFormDetails
            (@formuid AS int, @category as VARCHAR(75))
RETURNS VARCHAR(1000) 
AS
    RETURN 
      SUBSTRING(
        (SELECT ', ' + data
         FROM DNet.ejc_FormDetails 
         WHERE formuid = @formuid AND category = @category
         FOR XML PATH('')
        ), 3, 1000)

技巧是使用 FOR XML PATH('') - 这会返回 数据的串联列表 列和固定的 ', ' 分隔符。添加一个 SUBSTRING() 就完成了！就这么简单......没有顽固缓慢的 CURSOR，没有混乱的串联和所有那些粘糊糊的代码 - 只需一个语句即可。

Answer 2

您可以使用表值参数：

CREATE FUNCTION MyFunction(
    @Data AS TABLE (
        Column1 int,
        Column2 nvarchar(50),
        Column3 datetime
    )
)
RETURNS NVARCHAR(MAX)
AS BEGIN
    /* here you can do what you want */
END

Answer 3

从 SQL Server 2008 开始，您可以使用表值参数，这将允许您将 TABLE 变量作为参数传递。相关限制和示例都在该链接的文章中。

然而，我还要指出，使用游标很可能会降低性能。
您不需要使用游标，因为您可以在 1 个 SELECT 语句中完成所有操作：

SELECT @MyCSVString = COALESCE(@MyCSVString + ', ', '') + data 
FROM DNet.ejc_FormDetails 
WHERE formuid = @formuid AND category = @category

不需要游标

Answer 4

你的问题有点不清楚。在您的第一个 SQL 语句中，您似乎试图将列传递给函数，但没有 WHERE 子句。在第二个 SQL 语句中，您将传递行集合（来自 SELECT 的结果）。您能提供一些样本数据和预期结果吗？

在不完全理解您的目标的情况下，您可以考虑将参数更改为表变量。填充调用代码的本地表变量并将其传递到函数中。不过，您可以将其作为存储过程来执行，并且不需要函数。