ParametersParameters

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据:Parameters are used to exchange data between stored procedures and functions and the application or tool that called the stored procedure or function:

  • 输入参数允许调用方将数据值传递到存储过程或函数。Input parameters allow the caller to pass a data value to the stored procedure or function.
  • 输出参数允许存储过程将数据值或游标变量传递回调用方。Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. 用户定义函数不能指定输出参数。User-defined functions cannot specify output parameters.
  • 每个存储过程向调用方返回一个整数返回代码。Every stored procedure returns an integer return code to the caller. 如果存储过程没有显式设置返回代码的值,则返回代码为 0。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:

-- Create a procedure that takes one input parameter and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
         @MaxTotal 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, JobTitle
FROM HumanResources.vEmployee
WHERE EmployeeID = @EmployeeIDParm

-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Set a value in the output parameter.
SELECT @MaxTotal = MAX(TotalDue)
FROM Sales.SalesOrderHeader;

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 or function 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. 参数和返回代码可以与 Transact-SQL 变量或应用程序变量交换数据值。Parameters and return codes can exchange data values with either Transact-SQL variables or application variables.

如果从批处理或脚本调用存储过程,则参数和返回代码值可以使用在同一个批处理中定义的 Transact-SQL 变量。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. 下面是执行以前创建的过程的批处理的示例。The following example is a batch that executes the procedure created earlier. 输入参数被指定为常量,输出参数和返回代码将它们的值放在 Transact-SQL 变量中: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 @MaxTotalVariable INT

-- Execute the stored procedure and specify which variables
-- are to receive the output parameter and return code values.
EXEC @ReturnCode = SampleProcedure @EmployeeIDParm = 19,
   @MaxTotal = @MaxTotalVariable OUTPUT

-- Show the values returned.
PRINT ' '
PRINT 'Return code = ' + CAST(@ReturnCode AS CHAR(10))
PRINT 'Maximum Quantity = ' + CAST(@MaxTotalVariable AS CHAR(10))
GO

应用程序可以通过绑定到程序变量的参数标记在应用程序变量、参数和返回代码之间交换数据。An application can use parameter markers bound to program variables to exchange data between application variables, parameters, and return codes.

另请参阅See Also

CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
重用参数和执行计划部分 Parameters and Execution Plan Reuse section
变量 (Transact-SQL)Variables (Transact-SQL)