如何用动态T-SQL将EXECUTE命令结果插入参数化命名表？

问题场景 #

需要连接两张表并将结果存入参数化名称的目标表，为此编写了存储过程，但在实现结果插入时触发语法错误。@SQL_net对应的查询语句可正常执行，但添加@SQL_FINAL实现向参数化表插入数据时出错。

错误信息 #

• 关键字'select'附近有语法错误。
• ')'附近有语法错误。

生成的错误SQL语句 #

INSERT INTO sqlDB.dbo.leasing_monthly_series exec(select e.symbol,e.tracing_no,e.year
from sqlDB.dbo.income e
inner join sqlDB.dbo.cost g
on e.tracing_no=g.tracing_no)

原存储过程代码 #

ALTER PROCEDURE [dbo].[monthly_series_table] 
@INCOME_SUFFIX VARCHAR(MAX),
@COST_SUFFIX VARCHAR(MAX),
@TABLE_NAME VARCHAR(MAX),
@SECTION BIGINT

AS
BEGIN

    SET NOCOUNT ON;
    Declare @SQL_net varchar(max) =''
IF OBJECT_ID('sqlDB.dbo.income', 'U') IS NOT NULL
    DROP TABLE income; 
BEGIN 
    CREATE TABLE income (
                symbol varchar(max),
                tracing_no bigint,
                year bigint

        )
END


IF OBJECT_ID('sqlDB.dbo.cost', 'U') IS NOT NULL
    DROP TABLE cost; 
BEGIN 

    CREATE TABLE cost (
                symbol varchar(max),
                tracing_no bigint,
                year bigint

        )
END
DECLARE @SQL VARCHAR(MAX)=''
DECLARE @SQL_FINAL VARCHAR(MAX)=''

exec sqlDB.dbo.drop_table_exists @TABLE_NAME
BEGIN 
set @SQL='
    CREATE TABLE sqlDB.dbo.'+ @TABLE_NAME+' (
                symbol varchar(max),
                tracing_no bigint,
                year bigint

        )'
        
END
EXEC (@SQL)
INSERT INTO income
EXEC sqlDB.dbo.monthly_series @INCOME_SUFFIX,'cum_income','this_month_income',@SECTION

INSERT INTO cost
EXEC sqlDB.dbo.monthly_series @COST_SUFFIX,'cum_cost','this_month_cost',@SECTION

set @SQL_net='select e.symbol,e.tracing_no,e.year
    from sqlDB.dbo.income e 
    inner join sqlDB.dbo.cost g
    on e.tracing_no=g.tracing_no'

SET @SQL_FINAL='INSERT INTO sqlDB.dbo.'
+@TABLE_NAME+' exec('+@SQL_net+')'
Print @SQL_Final
exec (@SQL_FINAL)

END

解决方案 #

1. 修复动态SQL语法错误 #

错误根源是exec()内部的查询语句未被当作字符串解析，SQL引擎无法识别嵌套的select。无需嵌套exec，直接用INSERT...SELECT拼接动态SQL即可：

-- 替换原@SQL_FINAL的赋值语句
SET @SQL_FINAL='INSERT INTO sqlDB.dbo.'+@TABLE_NAME+' '+@SQL_net
exec (@SQL_FINAL)

如果一定要保留exec嵌套写法，需要给@SQL_net的内容添加转义后的单引号：

SET @SQL_FINAL='INSERT INTO sqlDB.dbo.'+@TABLE_NAME+' exec('''+@SQL_net+''')'

2. 优化临时表避免并发冲突 #

当前存储过程使用永久表income和cost，多会话同时执行时会互相干扰。建议改用会话级临时表（以#开头），每个会话的临时表相互独立：

-- 替换原有的income和cost表创建/删除逻辑
IF OBJECT_ID('tempdb.dbo.#income', 'U') IS NOT NULL
    DROP TABLE #income; 
CREATE TABLE #income (
    symbol varchar(max),
    tracing_no bigint,
    year bigint
)

IF OBJECT_ID('tempdb.dbo.#cost', 'U') IS NOT NULL
    DROP TABLE #cost; 
CREATE TABLE #cost (
    symbol varchar(max),
    tracing_no bigint,
    year bigint
)

-- 后续插入和查询对应改为临时表
INSERT INTO #income
EXEC sqlDB.dbo.monthly_series @INCOME_SUFFIX,'cum_income','this_month_income',@SECTION

INSERT INTO #cost
EXEC sqlDB.dbo.monthly_series @COST_SUFFIX,'cum_cost','this_month_cost',@SECTION

set @SQL_net='select e.symbol,e.tracing_no,e.year
    from #income e 
    inner join #cost g
    on e.tracing_no=g.tracing_no'

3. 可选：添加SQL注入防护 #

为避免恶意表名导致注入风险，可对@TABLE_NAME做合法性校验：

IF PATINDEX('%[^a-zA-Z0-9_]%', @TABLE_NAME) > 0
BEGIN
    RAISERROR('非法的表名格式', 16, 1)
    RETURN
END

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