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.

CREATE PROCEDURE dbo.spGetPersonsStateCount @in_and_out INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @in_and_out = COUNT(*) 
FROM  Person.Address 
WHERE StateProvinceID=@in_and_out;
END

DECLARE @in_and_out_param INT=79
SELECT @in_and_out_param
EXEC dbo.spGetPersonsStateCount @in_and_out_param OUTPUT
SELECT @in_and_out_param
Output:
-----------
79
-----------
2636

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.