CODEWITHSUNDEEP
sqlsql-server
user2292759
user2292759

Reputation: 159

Get SQL stored procedure output parameters via query

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

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!

Upvotes: 0

Views: 256

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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

is_output     bit     1 = Parameter is OUTPUT or RETURN; otherwise, 0.

From: https://learn.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;

Upvotes: 1

Related Questions