Output Stream of Consciousness

This is pretty simple to accomplish, though how exactly to do it can be hard to find.

Quite simply, you add OUTPUT to your parameter lists, on both your procedure definition and your actual call, like so:

Defining:

CREATE PROCEDURE dbo.TestValue
@Value int OUTPUT
AS
SET @Value = @Value + 1
GO

Calling:

DECLARE @Banana int
SET @Banana = 1
PRINT @Banana
EXEC dbo.TestValue @Banana OUTPUT
PRINT @Banana
EXEC dbo.TestValue @Banana OUTPUT
PRINT @Banana
GO

If you run this in SQL Server Management Studio, this will give you the output:

1
2
3

If you miss the OUTPUT on the calling side, your value will simply be unaffected, and will output instead:

1
1
1

If you miss the OUTPUT on the defining side, the OUTPUT on the calling side will generate an error:

Msg 8162, Level 16, State 2, Procedure TestValue, Line 0
The formal parameter "@Value" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.