如何创建按列类型插入默认值的SQL Server存储过程？

实现说明 #

SQL Server中无法直接将表对象作为存储过程参数，因此我们通过传入带架构的表名字符串实现需求。存储过程会自动识别表中int和nvarchar类型的列，分别插入-1和'Unknown'；其他数据类型列将使用自身默认值（无默认值则插入NULL）。

存储过程代码 #

CREATE OR ALTER PROCEDURE dbo.usp_InsertData
    @TableName NVARCHAR(256) -- 传入带架构的表名，例如 N'Dim.Name'
AS
BEGIN
    SET NOCOUNT ON;

    -- 验证目标表是否存在
    IF NOT EXISTS (SELECT 1 FROM sys.objects o 
                   JOIN sys.schemas s ON o.schema_id = s.schema_id
                   WHERE QUOTENAME(s.name) + '.' + QUOTENAME(o.name) = @TableName)
    BEGIN
        RAISERROR('指定的表不存在: %s', 16, 1, @TableName);
        RETURN;
    END

    -- 动态拼接INSERT语句
    DECLARE @InsertSql NVARCHAR(MAX);
    SELECT @InsertSql = 'INSERT INTO ' + @TableName + ' (' + 
                        STRING_AGG(QUOTENAME(c.name), ', ') + ') VALUES (' +
                        STRING_AGG(
                            CASE 
                                WHEN t.name = 'int' THEN '-1'
                                WHEN t.name = 'nvarchar' THEN '''Unknown'''
                                ELSE 'DEFAULT' -- 其他类型用列默认值，无默认值则插NULL
                            END, ', '
                        ) + ')'
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    JOIN sys.objects o ON c.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE QUOTENAME(s.name) + '.' + QUOTENAME(o.name) = @TableName;

    -- 执行动态SQL
    EXEC sp_executesql @InsertSql;
END
GO

调用示例 #

-- 给Dim.Name表插入默认值
EXEC dbo.usp_InsertData @TableName = N'Dim.Name';

关键细节 #

• 依赖sys.columns和sys.types系统视图获取列信息，适配所有用户表。
• STRING_AGG适用于SQL Server 2017及以上版本，低版本可替换为FOR XML PATH拼接逻辑。
• 用QUOTENAME处理表名、列名，避免SQL注入风险。
• 加入表存在性校验，防止执行无效操作。

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