MS SQL Server：存储过程实例

存储过程实例
1. T-SQL 创建存储过程

例1： 使用简单的存储过程

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO


uspGetEmployees 存储过程可通过以下方式执行：


EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;



例2：带有参数的 存储过程执行方法
--- Bad
EXEC DBO.PRO_TEST_2 2, 'B'
--- Good
EXEC DBO.PRO_TEST_2 @ID = 2, @NAME = 'B'



2. 使用 RETRUN
从存储过程中返回值一体式错误的方法之一是使用 RETURN 语句。给语句可以立刻停止存储过程的执行，并将控制权转移回到存储过程之外。因此，任何位于 RETURN 语句之后的语句都不会被执行。

使用 RETURN 示例：
CREATE PROCEDURE CustomerDetails.apf_CustBalances
        @CustID int,
        @ClearedBalance money OUTPUT , @UnclearedBalance money OUTPUT
AS
BEGIN
        SELECT @ClearedBalance = ClearedBalance,
                @UnclearedBalance = UnclearedBalance
        FROM Customers
        WHERE CustomerId = @CustID;
       
        RETURN @@Error



执行该存储过程：
DECLARE @ClearedBalance Money , @UnClearedBalance Money ;
DECLARE @RetVal int;
EXECUTE @RetVal = CustomerDetails.apf_CustBalances 1,
                                                @CleaeredBalance OUTPUT,
                                                @UnClearedBalance OUTPUT

SELECT @RetVal AS ReturnValue ,
        @CleaeredBalance AS CleaeredBalance,
        @UnClearedBalance AS UnClearedBalance
GO



3. 使用 OUTPUT 游标参数
OUTPUT 游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器。

首先，创建在 Currency 表上声明并打开一个游标的过程：
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO



接下来，运行以下批处理：声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。
USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO


4. 使用 WITH RECOMPILE 选项
如果为过程提供的参数不是典型的参数，并且新的执行计划不应被缓存或存储在内存中，则 WITH RECOMPILE 子句会很有用。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspproduct_by_vendor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v
    JOIN Purchasing.ProductVendor AS pv
      ON v.VendorID = pv.VendorID
    JOIN Production.Product AS p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO


5. 使用 WITH ENCRYPTION 选项
以下示例将创建 HumanResources.uspEncryptThis 存储过程。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO


如以下示例所示，WITH ENCRYPTION 选项可阻止返回存储过程的定义。
运行 sp_helptext：
EXEC sp_helptext 'HumanResources.uspEncryptThis';

下面是结果集：
The text for object 'HumanResources.uspEncryptThis' is encrypted.

直接查询 sys.sql_modules 目录视图：
USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
GO

下面是结果集：
definition
----------------------
NULL

(1 row(s) affected)



6. 使用延迟名称解析
以下示例将创建 uspProc1 过程。该过程使用延迟名称解析。尽管引用的表在编译时不存在，但仍能创建存储过程。但是，执行过程时表必须存在。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SELECT column1, column2 FROM table_does_not_exist
GO



若要验证是否已创建了存储过程，请运行以下查询：
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');



下面是结果集：
definition
------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)