Parameters
Parameters are used to exchange data between stored procedures and the application or tool that called the stored procedure:
- Input parameters allow the caller to pass a data value to the stored procedure.
- Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller.
- Every stored procedure returns an integer return code to the caller. If the stored procedure does not explicitly set a value for the return code, the return code is 0.
The following stored procedure shows the use of an input parameter, an output parameter, and a return code:
USE Northwind
GO
-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
@MaxQuantity INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0
-- Do a SELECT using the input parameter.
SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm
-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave
GO
When a stored procedure is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables.
If a stored procedure is called from a batch or script, the parameters and return code values can use Transact-SQL variables defined in the same batch. This example is a batch that executes the procedure created earlier. The input parameter is specified as a constant and the output parameter and return code place their values in Transact-SQL variables:
-- Declare the variables for the return code and output parameter.
DECLARE @ReturnCode INT
DECLARE @MaxQtyVariable INT
-- Execute the stored procedure and specify which variables
-- are to receive the output parameter and return code values.
EXEC @ReturnCode = SampleProcedure @EmployeeIDParm = 9,
@MaxQuantity = @MaxQtyVariable OUTPUT
-- Show the values returned.
PRINT ' '
PRINT 'Return code = ' + CAST(@ReturnCode AS CHAR(10))
PRINT 'Maximum Quantity = ' + CAST(@MaxQtyVariable AS CHAR(10))
GO
An application can use parameter markers bound to program variables to exchange data between application variables, parameters, and return codes.