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 }
] [ ,…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.


Leave a Reply

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