Get SQL stored procedure output parameters via query

Question

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!

sticky bit · Accepted Answer · 2018-06-10 23:12:01Z

1

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;

answered Jun 10, 2018 at 23:12

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2292759 Over a year ago

Ah! So simple and it was right there in front of me :p Thanks! Issue resolved.

SMor Over a year ago

@user2292759 Perhaps you can accept the reply as the answer?

Collectives™ on Stack Overflow

Get SQL stored procedure output parameters via query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related