IF (@QuantityOrdered < (SELECT QuantityOnHand
FROM Inventory
WHERE PartID = @PartOrdered) )
BEGIN
-- SQL statements to update tables and process order.
END
ELSE
BEGIN
-- SELECT statement to retrieve the IDs of alternate items
-- to suggest as replacements to the customer.
END
存储过程的这个用途的一个例证是 SQL Server 系统存储过程,它将用户从系统表中隔离出来。SQL Server 中包含一组系统存储过程,这些过程的名称通常以 sp_ 开头。这些系统存储过程支持运行 SQL Server 系统所需的所有管理任务。可以使用 Transact-SQL 中与管理相关的语句(如 CREATE TABLE)或系统存储过程来管理 SQL Server 系统,永远不必直接更新系统表。
存储过程和执行计划
在 SQL Server 6.5 版及更早的版本中,存储过程是对执行计划进行部分预编译的方法。在创建存储过程的同时,系统表内也存储了一个部分编译好的执行计划。执行存储过程比执行 SQL 语句更有效,因为 SQL Server 不必从头到尾编译执行计划,而只须优化该过程所存储的计划。同时,在 SQL Server 过程高速缓存中保留了完全编译好的存储过程执行计划,这意味着后面执行的存储过程可以使用预先编译好的执行计划。
SQL Server 2000 和 SQL Server 7.0 版在语句处理上做了许多修改,将存储过程的许多性能优点扩展到所有 SQL 语句。SQL Server 2000 和 SQL Server 7.0 在创建存储过程时不保存部分编译的计划。与任何其它的 Transact-SQL 语句一样,存储过程也在执行时进行编译。SQL Server 2000 和 SQL Server 7.0 在过程高速缓存内保留所有 SQL 语句的执行计划,而不只是存储过程的执行计划。数据库引擎使用一种高效的算法,将新的 Transact-SQL 语句与现有执行计划的 Transact-SQL 语句进行比较。如果数据库引擎确定新的 Transact-SQL 语句与现有执行计划的 Transact-SQL 语句相匹配,就重新使用这个计划。这样就将执行计划的重复使用性扩展到了所有 SQL 语句,从而减少了预编译存储过程的相对性能优势。
SQL Server 2000 和 SQL Server 7.0 版提供了新的处理 SQL 语句的可选方法。有关更多信息,请参见查询处理器构架。
临时存储过程
SQL Server 2000 还支持临时存储过程,这些过程与临时表一样,在连接断开时自动被除去。临时存储过程存储在 tempdb 内,它们在连接到 SQL Server 以前的版本时很有用。如果应用程序生成需要多次执行的动态 Transact-SQL 语句,就可以使用临时存储过程。无须每次重新编译 Transact-SQL 语句,而可以创建临时存储过程,在第一次执行时编译该过程,然后多次执行预先编译好的计划。不过,大量使用临时存储过程会导致在 tempdb 内争夺系统表。
SQL Server 2000 和 SQL Server 7.0 的两个功能消除了使用临时存储过程的需要:
最后通过带 SELECT 语句的 RETURN 语句返回整数。返回代码通常是用来传回错误检查信息的。此过程的执行没有错误,因此返回了另一个值说明所返回代码的填写方式。
USE Northwind
GO
DROP PROCEDURE OrderSummary
GO
CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
-- SELECT to return a result set summarizing
-- employee sales.
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID
-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]
-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO
-- Test the stored procedure.
-- DECLARE variables to hold the return code
-- and output parameter.
DECLARE @OrderSum INT
DECLARE @LargestOrder INT
-- Execute the procedure, which returns
-- the result set from the first SELECT.
EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT
-- Use the return code and output parameter.
PRINT 'The size of the largest single order was: ' +
CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
CONVERT(CHAR(6), @OrderSum)
GO
下例执行后的输出结果是:
EmployeeID SummSales
----------- --------------------------
1 202,143.71
2 177,749.26
3 213,051.30
4 250,187.45
5 75,567.75
6 78,198.10
7 141,295.99
8 133,301.03
9 82,964.00
The size of the largest single order was: 130
The sum of the quantities ordered was: 51317