下面的例子来自MSDN:http://msdn.microsoft.com/zh-cn/library/ms188655储存过程主体:
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
GO
调用储存过程:
DECLARE @SalesYTDBySalesPerson money;
EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
PRINT 'Year-to-date sales for this employee is ' + convert(varchar(10),@SalesYTDBySalesPerson);
GO
问题:
@SalesYTD参数是OUTPUT型,意味着可以传入值、传出值,可是,在储存过程主体中,@SalesYTD在等号左边啊,怎么能传入值呢?
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
GO
调用储存过程:
DECLARE @SalesYTDBySalesPerson money;
EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
PRINT 'Year-to-date sales for this employee is ' + convert(varchar(10),@SalesYTDBySalesPerson);
GO
问题:
@SalesYTD参数是OUTPUT型,意味着可以传入值、传出值,可是,在储存过程主体中,@SalesYTD在等号左边啊,怎么能传入值呢?
EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe',@SalesYTDBySalesPerson OUTPUT;也可以EXECUTE Sales.uspGetEmployeeSalesYTD @SalesPerson
= N'Blythe',@SalesYTD = @SalesYTDBySalesPerson OUTPUT;
DECLARE @SalesYTDBySalesPerson money;--都不用
EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe', @SalesYTDBySalesPerson OUTPUT;--都用
EXECUTE Sales.uspGetEmployeeSalesYTD @SalesPerson=N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;--前面不用后面用(注意前面不用后面可以用,但前面用了后面必须用)
EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe',@SalesYTD = @SalesYTDBySalesPerson OUTPUT;--颠倒用(颠倒了就必须要一一对应的用)
EXECUTE Sales.uspGetEmployeeSalesYTD @SalesYTD=@SalesYTDBySalesPerson OUTPUT,@SalesPerson=N'Blythe';--还有一种情况,当存储过程带有缺省值参数的时候,调用时缺省的参数可以不赋值,而直接用这种方式来跳过缺少参数传参