通过查询获取SQL存储过程输出参数

Here I have a sql script which retrieves all stored procedures and it's parameters.

这里我有一个sql脚本,它检索所有存储过程及其参数。

select * 
from sys.parameters 
inner join sys.procedures on parameters.object_id = procedures.object_id 
inner join sys.types on parameters.system_type_id = types.system_type_id 
                     and parameters.user_type_id = types.user_type_id
--where procedures.name = 'STORED_PROC_NAME'

What I want to do is to be able to grab the stored procedures output parameters this way if at all possible.

我想要做的是,如果可能的话,能够以这种方式获取存储过程输出参数。

If someone could point me to where in the sys catalog views I can find this information, it would be appreciated! Thanks!

如果有人能指出我在sys目录视图中的哪个位置我可以找到这些信息,我们将不胜感激!谢谢!

1 个解决方案

#1

The column is_output in sys.parameters is 1, when it is an output parameter.

sys.parameters中的列is_output是1,当它是输出参数时。

is_output     bit     1 = Parameter is OUTPUT or RETURN; otherwise, 0.
From: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-parameters-transact-sql?view=sql-server-2017

So change your query to:

所以将您的查询更改为:

select *
       from sys.parameters 
            inner join sys.procedures
                       on parameters.object_id = procedures.object_id 
            inner join sys.types
                       on parameters.system_type_id = types.system_type_id
                       and parameters.user_type_id = types.user_type_id
       where parameters.is_output = 1;

#1