---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 付録B ストアドプロシージャのコンパイル／保存／再利用
---------------------------------------------------------------------

---------------------------------------------------------------------
-- B.2 実行プランの再利用
---------------------------------------------------------------------

SELECT *
FROM Asset5.dbo.Inventory


SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitId, UserName
FROM Asset5.dbo.Contact
WHERE ContactId = 3


SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitId, UserName
FROM Asset5.dbo.Contact
WHERE ContactId = @P1


SELECT FirstName, LastName, Phone, Fax, Email, OrgUnitId, UserName
FROM Asset5.dbo.Contact
WHERE ContactId = 11

---------------------------------------------------------------------
-- B.3 ストアドプロシージャの再コンパイル
---------------------------------------------------------------------

EXEC sp_recompile dbo.ap_OrdersByCountry_List


EXEC sp_recompile Orders


CREATE PROCEDURE dbo.ap_OrdersByCountry_List
    @Country CHAR(3)
WITH RECOMPILE
AS
    SELECT * FROM dbo.Orders
    WHERE Country = @Country


EXEC dbo.ap_OrdersByCountry_List 'USA' WITH RECOMPILE

