SQL Server动态执行存储过程如何获取OUTPUT参数值

我有一个如下所示的存储过程：

CREATE PROCEDURE my_schema.sp_do   
    @param1 VARCHAR(50),   
    @param2 VARCHAR(MAX),
    @count INT OUTPUT
AS   
    -- execute logic
    SET @count = (SELECT col1 FROM @tbl)
GO

该存储过程执行逻辑后会将@count输出参数设置为某个值，我希望在调用时能获取该值。

我可以通过以下方式执行并获取输出值：

DECLARE @i INT
EXEC my_schema.sp_do 'val1', 'val2', @i OUTPUT;
PRINT @i

但实际使用中我需要动态构建查询语句，请问如何以字符串形式执行该查询并仍能访问输出参数？

例如：

DECLARE @i INT
SET @sql = 'EXEC my_schema.sp_do ''val1'', ''val2'', @i OUTPUT;'
exec sp_executesql @sql
PRINT @i

我知道这种写法无法生效，正确的实现方式是什么？

我了解到需要为sp_executesql的第二个参数传入参数名称和数据类型，但由于是动态构建查询，我无法获取数据类型，仅能获取参数名称和值。如果有更优的实现方案，我也愿意采纳。

补充说明 #

我需要处理多个存储过程，每个都有各自的参数，由多位数据库管理员定义，数量会增减，但所有存储过程都需包含可获取的count OUTPUT参数。用户通过应用配置这些存储过程的执行参数和别名，相关信息存储在元数据表中。我正在编写一个控制器，将定期查询元数据表并执行这些配置好的存储过程。通过元数据表我能构建存储过程路径并传入参数，但不确定如何获取参数的数据类型，是否有可行方法？

一、动态调用存储过程并获取输出参数的正确写法 #

要通过sp_executesql获取输出参数，必须显式声明参数类型，并将外部变量与动态SQL中的占位符绑定，不能直接在动态字符串中引用外部变量。示例如下：

DECLARE @i INT
DECLARE @sql NVARCHAR(MAX)

SET @sql = N'EXEC my_schema.sp_do @param1 = ''val1'', @param2 = ''val2'', @count = @i OUTPUT;'

-- 声明参数映射，指定@i的类型为INT OUTPUT
EXEC sp_executesql 
    @sql,
    N'@i INT OUTPUT',
    @i = @i OUTPUT;

PRINT @i

核心要点：

• 动态SQL中使用占位符（如@i）替代直接引用外部变量
• 通过sp_executesql的第二个参数定义参数的名称和数据类型
• 执行时将外部变量与占位符绑定，并指定OUTPUT关键字

二、从系统元数据获取存储过程参数类型 #

针对多存储场景，可以查询SQL Server系统视图获取参数的完整信息，包括名称、数据类型、是否为输出参数等：

SELECT 
    SCHEMA_NAME(p.schema_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS proc_name,
    prm.parameter_id,
    prm.name AS parameter_name,
    TYPE_NAME(prm.system_type_id) AS data_type,
    prm.max_length,
    prm.precision,
    prm.scale,
    CASE WHEN prm.is_output = 1 THEN 'YES' ELSE 'NO' END AS is_output
FROM 
    sys.procedures p
JOIN 
    sys.parameters prm ON p.object_id = prm.object_id
WHERE 
    OBJECT_NAME(p.object_id) = 'sp_do' -- 替换为目标存储过程名
    AND SCHEMA_NAME(p.schema_id) = 'my_schema'; -- 替换为目标架构名

集成到控制器逻辑的步骤 #

1. 定期从元数据表读取待执行的存储过程列表
2. 对每个存储过程，执行上述查询获取所有参数信息（重点标记is_output = 1的参数）
3. 根据获取到的参数类型，动态构建sp_executesql的参数声明部分
4. 绑定输入参数值和输出参数变量，执行动态SQL并获取输出值

三、简化实现（针对固定输出参数类型场景） #

如果所有存储过程的count输出参数固定为INT类型，可直接简化逻辑，无需查询系统元数据：

DECLARE @proc_name NVARCHAR(255) = 'my_schema.sp_do'
DECLARE @input_params NVARCHAR(MAX) = '@param1 = ''val1'', @param2 = ''val2'''
DECLARE @count INT
DECLARE @sql NVARCHAR(MAX)

SET @sql = N'EXEC ' + @proc_name + ' ' + @input_params + ', @count = @output_count OUTPUT;'

EXEC sp_executesql
    @sql,
    N'@output_count INT OUTPUT',
    @output_count = @count OUTPUT;

PRINT @count

这种方式省去了查询系统元数据的开销，适合输出参数类型统一的场景。

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