如何修复xp_cmdshell与OPENROWSET中‘+’附近的语法错误

问题根源 #

T-SQL里，xp_cmdshell、OPENROWSET这类系统存储过程/函数不支持直接在参数位置使用字符串拼接表达式（+或CONCAT），必须先把完整的命令或SQL语句组装到变量中，再通过动态SQL执行。

1. 修复xp_cmdshell的代码 #

先将DIR命令拼接为完整字符串变量，再传入xp_cmdshell执行：

DECLARE @dirCmd NVARCHAR(MAX);
-- 组装完整的DIR命令
SET @dirCmd = 'DIR ' + QUOTENAME(@folderPath, '''') + '*.xlsx /B /A:-D';
-- 执行命令并插入结果到表中
INSERT INTO @files (FilePath)
EXEC xp_cmdshell @dirCmd;

2. 修复OPENROWSET的代码 #

构造动态SQL语句时，注意动态SQL中的单引号需要用两个单引号转义：

DECLARE @openRowSetSql NVARCHAR(MAX);
-- 组装包含OPENROWSET的完整插入语句
SET @openRowSetSql = N'INSERT INTO #tempTable (ColumnName, ColumnData)
                       SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', 
                       ''Excel 12.0;Database=' + QUOTENAME(@filePath, '''') + ';HDR=YES'', 
                       ''SELECT * FROM [Sheet1$]'')';
-- 执行动态SQL
EXEC sp_executesql @openRowSetSql;

额外说明（适配动态列名需求） #

由于你每次导入的Excel列名不同，后续创建表、转换数据的逻辑也需要依赖动态SQL：

1. 可以先通过OPENROWSET读取Excel的列名信息：

DECLARE @getColumnsSql NVARCHAR(MAX);
SET @getColumnsSql = N'SELECT COLUMN_NAME 
                       FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', 
                       ''Excel 12.0;Database=' + QUOTENAME(@filePath, '''') + ';HDR=YES'', 
                       ''SELECT * FROM [Sheet1$]'') 
                       INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = ''Sheet1$''';
EXEC sp_executesql @getColumnsSql;

2. 用获取到的列名动态生成CREATE TABLE语句，将Excel数据导入新表后，再执行转换逻辑写入最终表。

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