Share via

TSQL Basics II - Parameter Passing Semantics

  • Article

TSQL uses a copy-in/copy-out semantics to pass parameters to stored procedures and functions.  This is a different semantics then the usual by-value or by-reference parameter passing semantics supported by most languages.

What does this mean?
When a parameter is passed to a stored proc, a local copy of the parameter is made for the stored proc to use.  This is no different then the by-value semantics of most programming languages. Where TSQL differs is that when the stored proc finishes executing (without hitting an error) a copy-out is made which updates the parameter passed in with any changes that were made to it in the stored proc. In this manner it appears to the caller of the stored proc that the parameter was passed by reference, since any changes made to the parameter in the stored proc propagate back to the caller when the stored proc finishes executing successfully.

Why is it done this way?
The real benefit of this approach is in the error case.  If an error occurs in the middle of a stored procedure's execution, any changes made to parameters will not propagate back to the caller.  This makes error handling much easier as changes made in stored procedures are isolated.  If the parameters had been passed by reference (i.e., a copy-in didn't occur, the stored proc just modifies the actual parameter passed in), on an error we could never trust  any parameter's value as they may reflect the partial execution of the stored procedure. Another advantage of copy-in\copy-out is that it works the same no matter if the stored proc is called remotely or called from within the server (this wouldn't be the case if TSQL used by-ref semantics which are impossible for stored procs called remotely).

But what about OUTPUT?
The OUTPUT keyword controls the copy out of the parameter.  If the OUTPUT keyword is not present no copy-out is made.  Passing a parameter without the OUTPUT keyword is equivalent to the pass by value semantics used in many other programming languages.

Example:

CREATE PROC SP (@p1 int OUTPUT) AS
BEGIN
 SET @p1 = @p1 + 10;
 SELECT * FROM nonExistentTable;
END
go

DECLARE @v int;
SET @v = 1;
EXEC SP @v OUTPUT;
PRINT @v;
go

SP will hit an error when trying to run the SELECT statement (nonExistentTable doesn't exist). Thus, becuase of TSQL's copy-in/copy-out semantics 1 is printed out as a result of the execution.  The SET statement within SP is executed, but sets a copy of @v (that is stored in @p1) to 11.  Thus, when an error was encountered executing the SELECT statement this new value was not copied back out leaving the value of @v at 1. If TSQL used by reference semantics, then executing the batch would print 11, as the SET statement would have updated @v directly (as @p refers directly to @v - not to a copy).

The bottom line:
Parameters to stored procs never reflect the partial execution of the stored proc if it encountered an error.

Comments