SQL Server存储过程导出CSV表头位置异常，如何固定表头为第一行？

问题根源 #

你的存储过程出现表头位置/列顺序随机的原因主要有两点：

• 从tempdb.INFORMATION_SCHEMA.Columns查询列名时，未指定按列的原始创建顺序排序，系统视图返回的列顺序不固定
• 拼接Union查询时存在字符串语法错误，可能导致排序字段temp##SortID无法正常生效，进而让表头行位置不确定

修改后的存储过程代码 #

CREATE PROCEDURE [dbo].[spExportData_n]
    (@dbName varchar(100),
     @sql varchar(8000),
     @fullFileName varchar(1000))
AS
    IF @sql = '' or @fullFileName = ''
    BEGIN
        SELECT 0 AS ReturnValue -- 执行失败
        RETURN 
    END

    -- 若未传入数据库名则默认使用master
    SELECT @dbName = 'use ' + @dbName + ';'

    IF object_id('##TempExportData') IS NOT NULL
       DROP TABLE ##TempExportData

    IF object_id('##TempExportData2') IS NOT NULL
       DROP TABLE ##TempExportData2

    -- 将数据插入全局临时表
    DECLARE @columnNames varchar(8000), 
            @columnConvert varchar(8000),  
            @tempSQL varchar(8000)

    SELECT @tempSQL = LEFT(@sql, CHARINDEX('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql), len(@sql)) -- 修复原代码中substring起始位置错误

exec(@dbName + @tempSQL)
if @@error > 0
begin
select 0 as ReturnValue -- 执行失败
return
end

-- 构建列名列表和转换后的列列表（按原始列顺序排序）
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
ORDER BY ORDINAL_POSITION -- 关键：按列的原始顺序排序

-- 拼接查询语句，确保表头行在第一行
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + REPLACE(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'

exec (@sql)

-- 构建BCP命令
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp "SELECT * from ##TempExportData2" queryout ' + @fullFileName + ' -c -t, -U *** -P ****'
EXEC MASTER..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- 执行失败
return
end

DROP TABLE ##TempExportData
DROP TABLE ##TempExportData2
SELECT 1 as ReturnValue

关键修改点说明 #

1. 按原始列顺序获取列名：在查询tempdb.INFORMATION_SCHEMA.Columns时添加ORDER BY ORDINAL_POSITION，确保列名和转换列的顺序与原始SQL返回的顺序完全一致，避免表头列顺序随机。
2. 修复Substring起始位置：原代码中substring起始位置多取了一个字符，修正后保证临时表创建语句语法正确。
3. 修复Union查询的字符串拼接错误：修正引号转义问题，确保表头行的temp##SortID为1、数据行为2，排序后表头固定在第一行。
4. 简化BCP命令：移除重复的-c参数，修正用户名和密码的分隔格式，避免命令执行异常。

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