INPUT and OUTPUT type parameters in stored procedures

Output-only parameters do not exist in the T-SQL procedures. They are all either input-only or input/output.

The OUTPUT keyword in the procedure’s definition or its invocation, designates the input/output type of a parameter.

The shorten CREATE syntax for stored procedure is the following

CREATE { PROC | PROCEDURE }   procedure_name
[ { @parameter data_type }
[ OUT | OUTPUT]
] [ ,…n ]
AS { [ BEGIN ] sql_statement [;] [ …n ] [ END ] }

where the OUT | OUTPUT
Indicates that the parameter is an output parameter. Use OUTPUT parameters to return values to the caller of the procedure…

Actually, the parameters used with the OUTPUT keyword can at the same time be input parameters as well.

In this example the @in_and_out (input/output) parameter is assigned an input value of 79 which in the stored procedure is used to filter out some rows of the Person.Address table. The COUNT from the query is returned as output result to the same parameter.

Honestly, I don’t like this design of a stored procedure, but this post is motivated from a colleague that needed exactly this usage of the input/output parameters. He said that it simplifies his solution and he’s caring about the passing values for the parameter.

So if such situation ever happens to you, I hope this helps to you too.

 

One Reply to “INPUT and OUTPUT type parameters in stored procedures”

  1. Your colleague is deluded! This does not simplify things at all. It is a bad idea for a whole host of reasons.
    1) The parameters should be named for what they mean.
    2) you are making a single parameter have a generic name and mean more than one thing so you don’t know whether it is the stateprovince id or the result.
    3) the output may end up being the input because there is no corresponding stateprovinceID.

    You definitely need this to be take an input only parameter of @StateProvinceID INT
    and to have an input output parameter @Result or @PersonCount or some such.
    There’s probably many more good reasons for not doing what your friend suggested.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.