如何创建返回逗号分隔字段名的SQL函数？（替代存储过程）

Hey there! Let's work through this problem—since SQL Server scalar/inline functions block dynamic SQL directly, we need a few clever workarounds to get the comma-separated column list you want from a function. First, quick heads-up: your original stored procedure has a small join bug (you wrote a.schema_id = a.schema_id instead of a.schema_id = b.schema_id when linking sys.tables to sys.schemas)—I'll fix that in all the solutions below to ensure accuracy.

Option 1: Multi-Statement Table-Valued Function with OPENQUERY (Requires Minor Configuration) #

This approach uses OPENQUERY to run cross-database queries without hardcoding the database name, since we can't use dynamic SQL directly in a function. First, you'll need to enable Ad Hoc Distributed Queries (run this once):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Then create the table-valued function:

CREATE FUNCTION dbo.fn_generate_column_name_string
(
    @database NVARCHAR(100),
    @schema NVARCHAR(100),
    @table NVARCHAR(100)
)
RETURNS @Result TABLE (ColumnList NVARCHAR(MAX))
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @linkedServer NVARCHAR(100) = @@SERVERNAME;

    -- Build the query with QUOTENAME to prevent SQL injection
    SET @sql = N'
        SELECT ColumnList = STUFF((
            SELECT '','' + c.name
            FROM ' + QUOTENAME(@database) + '.sys.tables a
            JOIN ' + QUOTENAME(@database) + '.sys.schemas b ON a.schema_id = b.schema_id
            JOIN ' + QUOTENAME(@database) + '.sys.columns c ON c.object_id = a.object_id
            WHERE b.name = @schema AND a.name = @table
            FOR XML PATH(''''), TYPE
        ).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')';

    -- Use OPENQUERY to execute the dynamic query
    SET @sql = N'INSERT INTO @Result EXEC OPENQUERY(' + QUOTENAME(@linkedServer) + ', N''' + REPLACE(@sql, '''', '''''') + ''')';

    EXEC sp_executesql @sql, N'@schema NVARCHAR(100), @table NVARCHAR(100)', @schema = @schema, @table = @table;

    RETURN;
END;
GO

To use it, just call:

SELECT ColumnList FROM dbo.fn_generate_column_name_string('test', 'dbo', 'jl1_tmp');

Option 2: CLR Function (More Reliable, No Ad Hoc Query Setup) #

If your SQL Server allows CLR integration, this is a cleaner, more flexible solution. CLR functions can safely handle dynamic SQL behind the scenes.

1. First enable CLR integration (run once):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

2. Create a simple C# class library (this code connects to the target database and runs the column list query):

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

public class ColumnListTools
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString GetColumnList(SqlString database, SqlString schema, SqlString table)
    {
        if (database.IsNull || schema.IsNull || table.IsNull)
            return SqlString.Null;

        // Use trusted connection; adjust if you need SQL auth
        string connString = $"Data Source=(local);Initial Catalog={database.Value};Integrated Security=True;";
        string query = @"
            SELECT STUFF((
                SELECT ',' + c.name
                FROM sys.tables a
                JOIN sys.schemas b ON a.schema_id = b.schema_id
                JOIN sys.columns c ON c.object_id = a.object_id
                WHERE b.name = @schema AND a.name = @table
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')";

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@schema", schema.Value);
                cmd.Parameters.AddWithValue("@table", table.Value);
                object result = cmd.ExecuteScalar();
                return result == DBNull.Value ? SqlString.Null : new SqlString(result.ToString());
            }
        }
    }
}

3. Compile the C# code to a DLL, then register it in SQL Server:

CREATE ASSEMBLY ColumnListTools
FROM 'C:\Your\DLL\Path\ColumnListTools.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS; -- Use SAFE if you don't need cross-database access

CREATE FUNCTION dbo.fn_generate_column_name_string_clr
(
    @database NVARCHAR(100),
    @schema NVARCHAR(100),
    @table NVARCHAR(100)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME ColumnListTools.ColumnListTools.GetColumnList;
GO

Call it like a regular scalar function:

SELECT dbo.fn_generate_column_name_string_clr('test', 'dbo', 'jl1_tmp');

Option 3: Quick Workaround—Keep the Stored Procedure #

If you can't use the above methods, you can stick with your stored procedure and pull its result into a query using a temporary table:

CREATE TABLE #TempColumns (ColumnList NVARCHAR(MAX));
INSERT INTO #TempColumns EXEC dbo.usp_generate_column_name_string 'test', 'dbo', 'jl1_tmp';
SELECT ColumnList FROM #TempColumns;
DROP TABLE #TempColumns;

内容的提问来源于stack exchange，提问作者James Lester