如何将仅返回非空列的T-SQL查询封装为可复用函数？

核心限制说明 #

T-SQL的内置标量/表值函数不允许执行动态SQL（EXEC或sp_executesql），所以无法直接在函数内生成并执行动态查询返回结果集。推荐两种实用实现方式：

方式1：封装为标量函数（返回NOT NULL列列表） #

这个函数负责获取指定表的所有NOT NULL列名（逗号分隔），你可以在外部拼接动态SQL执行查询。

创建函数 #

CREATE FUNCTION dbo.GetNotNullColumns(@TableName NVARCHAR(256))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ColumnList NVARCHAR(MAX) = '';

    -- SQL Server 2017+ 使用STRING_AGG简化拼接
    SELECT @ColumnList = STRING_AGG(c.name, ', ')
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name + '.' + t.name = @TableName
      AND c.is_nullable = 0;

    -- 兼容SQL Server 2016及更早版本，替换上面的STRING_AGG为以下代码：
    /*
    SELECT @ColumnList = STUFF((SELECT ', ' + c.name
                               FROM sys.columns c
                               JOIN sys.tables t ON c.object_id = t.object_id
                               JOIN sys.schemas s ON t.schema_id = s.schema_id
                               WHERE s.name + '.' + t.name = @TableName
                                 AND c.is_nullable = 0
                               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
    */

    RETURN @ColumnList;
END;

使用示例 #

-- 获取test1表的NOT NULL列并执行查询
DECLARE @TableName NVARCHAR(256) = 'dbo.test1';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT ' + dbo.GetNotNullColumns(@TableName) + ' FROM ' + @TableName;
EXEC sp_executesql @SQL;

方式2：封装为存储过程（直接返回结果集） #

如果希望直接执行并返回结果，存储过程是更合适的选择，因为它原生支持动态SQL。

创建存储过程 #

CREATE PROCEDURE dbo.SelectNotNullColumns
    @TableName NVARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @SchemaName NVARCHAR(128);
    DECLARE @PureTableName NVARCHAR(128);

    -- 拆分Schema和表名，避免SQL注入风险
    IF CHARINDEX('.', @TableName) > 0
    BEGIN
        SET @SchemaName = LEFT(@TableName, CHARINDEX('.', @TableName) - 1);
        SET @PureTableName = SUBSTRING(@TableName, CHARINDEX('.', @TableName) + 1, LEN(@TableName));
    END
    ELSE
    BEGIN
        SET @SchemaName = 'dbo';
        SET @PureTableName = @TableName;
    END

    -- 获取NOT NULL列列表
    SELECT @ColumnList = STRING_AGG(c.name, ', ')
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name = @SchemaName
      AND t.name = @PureTableName
      AND c.is_nullable = 0;

    -- 生成并执行安全的动态SQL
    IF @ColumnList IS NOT NULL
    BEGIN
        DECLARE @SQL NVARCHAR(MAX) = 'SELECT ' + @ColumnList + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@PureTableName);
        EXEC sp_executesql @SQL;
    END
    ELSE
    BEGIN
        RAISERROR('指定的表不存在或没有定义NOT NULL约束的列', 16, 1);
    END
END;

使用示例 #

-- 直接查询test1表的NOT NULL列
EXEC dbo.SelectNotNullColumns @TableName = 'dbo.test1';

关于WHERE子句的疑问 #

不需要在WHERE子句中使用此函数。我们的需求是选择表中架构层面定义为NOT NULL的列（即创建表时加了NOT NULL约束的列），这属于SELECT子句的列选择逻辑，而WHERE子句是用来筛选符合条件的数据行，两者完全无关。

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