如何实现以SQL查询为参数的存储过程并传递查询结果？

你的代码存在两个核心问题：

1. SELECT * INTO #queryTable EXEC (@sqlCommand) 语法错误，且本地临时表在动态SQL的执行上下文创建后，外部存储过程无法访问；
2. 不能直接将临时表作为参数传递给存储过程，存储过程参数不支持直接传入表对象（除非使用预定义的表值参数）。

以下是几种可行的实现方式，按通用性排序：

方案一：全局临时表 + 动态SQL（通用，支持任意结果结构） #

这种方式通过生成唯一命名的全局临时表存储动态SQL结果，再动态调用目标存储过程处理该表，同时避免并发冲突。

主存储过程修改 #

CREATE PROCEDURE dbo.uspTaskRouter 
    @sqlCommand nvarchar(MAX),
    @uspName nvarchar(MAX),
    @dataType nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 验证目标存储过程存在，防止SQL注入
    IF NOT EXISTS (SELECT 1 FROM sys.procedures WHERE name = @uspName)
    BEGIN
        RAISERROR('存储过程 %s 不存在', 16, 1, @uspName);
        RETURN;
    END

    -- 生成唯一全局临时表名，避免并发冲突
    DECLARE @tempTableName nvarchar(128) = '##tmp_' + CAST(NEWID() AS nvarchar(36));
    
    -- 动态创建全局临时表并插入动态SQL结果（替换单引号避免语法错误）
    DECLARE @createInsertSql nvarchar(MAX) = 
        N'SELECT * INTO ' + @tempTableName + N' FROM (' + REPLACE(@sqlCommand, '''', '''''') + N') AS src';
    
    EXEC sp_executesql @createInsertSql;

    -- 动态拼接调用目标存储过程的语句
    DECLARE @execUpsSql nvarchar(MAX) = 
        N'EXEC ' + @uspName + N' ''' + @tempTableName + N''', ''' + REPLACE(@dataType, '''', '''''') + N'''';
    
    EXEC sp_executesql @execUpsSql;

    -- 清理全局临时表
    DECLARE @dropSql nvarchar(MAX) = N'DROP TABLE IF EXISTS ' + @tempTableName;
    EXEC sp_executesql @dropSql;
END

目标存储过程示例 #

目标存储过程需要接收全局临时表名作为参数，动态处理数据：

CREATE PROCEDURE dbo.YourTargetProcedure
    @tempTableName nvarchar(128),
    @dataType nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 示例：查询临时表数据并处理
    DECLARE @processSql nvarchar(MAX) = 
        N'SELECT * FROM ' + @tempTableName + N' WHERE 1=1'; -- 替换为你的实际处理逻辑
    
    EXEC sp_executesql @processSql;
END

方案二：表值参数（仅适用于结果结构固定场景） #

如果动态SQL的返回结果结构固定，可以预先定义用户表类型，通过表值参数传递数据，这种方式更安全且性能更好。

步骤1：定义表类型 #

CREATE TYPE dbo.YourResultType AS TABLE (
    Column1 INT,
    Column2 NVARCHAR(50),
    -- 按动态SQL实际返回列定义所有字段
);

步骤2：主存储过程修改 #

CREATE PROCEDURE dbo.uspTaskRouter 
    @sqlCommand nvarchar(MAX),
    @uspName nvarchar(MAX),
    @dataType nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 验证目标存储过程存在
    IF NOT EXISTS (SELECT 1 FROM sys.procedures WHERE name = @uspName)
    BEGIN
        RAISERROR('存储过程 %s 不存在', 16, 1, @uspName);
        RETURN;
    END

    DECLARE @resultTable dbo.YourResultType;

    -- 动态SQL插入数据到表值参数
    DECLARE @insertSql nvarchar(MAX) = 
        N'INSERT INTO @resultTable EXEC (' + REPLACE(@sqlCommand, '''', '''''') + N')';
    
    EXEC sp_executesql @insertSql, N'@resultTable dbo.YourResultType READONLY', @resultTable = @resultTable;

    -- 调用目标存储过程，传入表值参数
    DECLARE @execUpsSql nvarchar(MAX) = 
        N'EXEC ' + @uspName + N' @resultTable, ''' + REPLACE(@dataType, '''', '''''') + N'''';
    
    EXEC sp_executesql @execUpsSql, N'@resultTable dbo.YourResultType READONLY', @resultTable = @resultTable;
END

步骤3：目标存储过程修改 #

CREATE PROCEDURE dbo.YourTargetProcedure
    @resultTable dbo.YourResultType READONLY,
    @dataType nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 直接处理表值参数数据
    SELECT * FROM @resultTable; -- 替换为你的实际处理逻辑
END

注意事项 #

• SQL注入风险：所有动态拼接的SQL都需要用REPLACE替换单引号，同时验证传入的存储过程名合法性，避免恶意注入。
• 并发问题：方案一中使用NEWID生成唯一全局临时表名，可避免不同会话间的表名冲突。
• 权限：使用全局临时表需要确保执行存储过程的账号有足够权限；表值参数方案需要权限创建表类型。

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