如何创建一个返回逗号分隔值列表的函数？

Question

我正在尝试创建一个函数，该函数根据表名和列名参数返回逗号分隔的值列表。

这个想法是动态构建动态 SQL 语句，然后返回结果。但是，我遇到了一个问题，它不允许我在函数内部运行 execute 语句。这就是我的情况：

ALTER FUNCTION fn_HearingAttendeesToCSV (@TableName varchar(100), 
                                         @ColumnName varchar(100))
RETURNS VARCHAR(MAX)
AS
BEGIN
declare @sql varchar(max)

select @sql = 
'DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr + '', '' ,'''') + ' 
          + @ColumnName + ' FROM ' + @TableName + ' RETURN @listStr '

Exec (@sql)
END
GO

我很确定我走错了路。有人可以建议如何解决 execute 问题吗？或者有更好的方法来做到这一点？

Answer 1

您不能在函数期间执行此操作。函数无法执行动态 SQL，因此使用任意表/列名称（非确定性）与 SQL Server 函数背道而驰。

Answer 2

我认为您无法通过 T-SQL 来完成此操作，但您绝对可以通过用 C# 编写的 UDF 来完成同样的事情。代码如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {
   [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
   public static SqlString fn_HearingAttendeesToCSV(SqlString tableName, SqlString columnName) {
      System.Text.StringBuilder result = null;
      using (var cnn = new SqlConnection("context connection=true")) {
         var cmd = new SqlCommand();
         cmd.CommandText = String.Format("select [{0}] from [{1}]", SqlEscape(columnName.Value), SqlEscape(tableName.Value));
         cmd.Connection = cnn;

         cnn.Open();
         using (var rdr = cmd.ExecuteReader()) {
            while (rdr.Read()) {
               if (result == null) {
                  result = new System.Text.StringBuilder();
               }
               else {
                  result.Append(",");
               }
               result.Append(CsvEscape(rdr[0]));
            }
         }
         cnn.Close();
      }

      if (result == null) {
         return SqlString.Null;
      }
      else {
         return result.ToString();
      }
   }

   private static string SqlEscape(string s) {
      s = s ?? "";
      return s.Replace("[", "[[").Replace("]", "]]");
   }

   private static string CsvEscape(object o) {
      var s = o == null ? "" : o.ToString();
      return "\"" + s.Replace("\"", "\"\"") + "\"";
   }
};

为了使用 CLR UDF，您需要确保已在服务器上启用它们，如下所示：

exec sp_configure 'clr enabled', 1
RECONFIGURE

您还需要编译为旧版本的 .NET 框架（我测试的是 2.0）： SQL 2005 不允许 4.0 程序集。

即使您的 UDF 是用 C# 编写的，您仍然可以按照习惯的方式通过 T-SQL 访问它：

select fn_HearingAttendeesToCSV('table', 'column')

如果您需要支持“dbo”以外的架构，或者不需要纯 CSV，请修改为满足您的需求，但这应该可以让您实现 99% 的目标。也就是说，假设您是 DBA 或者您与他/她是足够好的朋友来启用 CLR。

Answer 3

您好，我认为他们关于函数的说法是正确的，但是您可以使用带有输出参数的 SP 来执行此操作。

检查此代码：

create proc DynCsv 
    @table  varchar(100),
    @column varchar(100),
    @csv    varchar(8000) output
as 
    declare
            @sql    nvarchar(4000),
            @parms  nvarchar(100)

    -- setup parms 
    select  @csv = '',          
            @sql = '
    select top 10 @csv = @csv +'','' + CAST(' + @column + ' as varchar)
    from ' + @table + ' option(maxdop 1)',
            @parms = '@csv varchar(8000) output'

    -- execute dynamic
    exec sp_executesql 
            @sql,
            @parms,
            @csv = @csv output

    -- loose first comma    
    set @csv = substring(@csv, 2, 8000)

您可以使用此代码调用它：

declare @csv varchar(8000)
exec dynCsv 'tObj', 'ObjId', @csv output
print @csv

祝你好运，

GJ

Answer 4

您不能在函数中执行此操作，但可以在 PROCEDURE 中执行此操作。我已经这样做过很多次来构建动态 SQL 语句。

这就是要走的路。