Search code examples
sql-serverinformation-schema

What is the difference between OUT and INOUT parameters and how does it relate to parameter_id

First of all I do understand the theoretical difference here between In, Out, and InOut. The questions I could find all focused on the general meaning of the terms, not the specifics I'm looking for here.

I'm querying stored procedure info from sys.parameters (because I want to link on object_id which the information-schema views don't support). I was looking at the INFORMATION_SCHEMA.PARAMETERS views for reference and noticed the following code:

convert(nvarchar(10), CASE
    WHEN c.parameter_id = 0 THEN 'OUT'
    WHEN c.is_output = 1 THEN 'INOUT'
    ELSE 'IN' END)                  AS PARAMETER_MODE,

So I'm somewhat less clear than when I started - what does parameter_id have to do with this and is there any real difference between OUT and INOUT in SqlServer?

Solution

  • After doing some further reading I think I see the answer:

    From the ms article on sys-parameters:
    Contains a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

    So all parameters marked "OUT" are technically "INOUT", however the special case of function return values results in an unnamed "OUT" parameter.