Retrieve input and output parameters for SQL stored procs and functions?

Question

For a given SQL stored proc or function, I'm trying to obtain its input and output parameters, where applicable, in a Winforms app I'm creating to browse objects and display their parameters and other attributes.

So far I've discovered the SQL system function object_definition, which takes a given sysobjects.id and returns the text of that object; also discovered via search this post which describes extracting the parameters in the context of a app using the ADO.NET method DeriveParameters in conjunction with some caching for better performance; and for good measure found some helpful system stored procs from this earlier post on Hidden Features of SQL Server.

I'm leaning towards implementing the DeriveParameters method in my C# app, since parsing the output of object_definition seems messy, and I haven't found a hidden feature in that post so far that would do the trick.

Is DeriveParameters applicable to both functions and stored procs for purposes of retreiving their parameters, and if so, could someone please provide an example?

Raja · Accepted Answer · 2010-04-22 21:24:20Z

2

Please try this:

select
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
PARAMETER_MODE,
NUMERIC_PRECISION,
NUMERIC_SCALE
from INFORMATION_SCHEMA.PARAMETERS
where specific_name = @chvProcName
order by ordinal_position

look for PARAMETER_MODE it will have a value of OUT

Got this info from here

HTH

answered Apr 22, 2010 at 21:24

Raja

3,6383 gold badges31 silver badges40 bronze badges

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

1 Comment

Darth Continent Over a year ago

Thanks, this is definitely helpful; if I don't see any responses specific to the DeriveParameters method in .NET, I'll award you the answer.

Collectives™ on Stack Overflow

Retrieve input and output parameters for SQL stored procs and functions?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related