---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第20章 Web検索エンジンのためのストアドプロシージャ
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 20.1 単純なソリューション
---------------------------------------------------------------------

SELECT Inventoryid, Make,            Model,
       EqType,      Location,        FirstName,
       LastName,    AcquisitionType, Address,
       City,        ProvinceId,      Country,
       Phone,       Fax,             Email,
       UserName,    OrgUnit
FROM dbo.Inventory Inventory
  INNER JOIN dbo.Contact Contact
  ON Inventory.OwnerId = Contact.ContactId
    INNER JOIN dbo.Location Location
    ON Inventory.LocationId = Location.LocationId
      INNER JOIN dbo.AcquisitionType AcquisitionType
      ON AcquisitionType.AcquisitionTypeId = Inventory.AcquisitionTypeId
        INNER JOIN dbo.Equipment Equipment
        ON Equipment.EqId = Inventory.EqId
          INNER JOIN dbo.EqType EqType
          ON Equipment.EqTypeId = EqType.EqTypeId
            INNER JOIN dbo.OrgUnit OrgUnit
            ON Contact.OrgUnitId = OrgUnit.OrgUnitId
WHERE Make = 'Compaq'
  AND EqType = 'Storage Array'
ORDER BY Country, ProvinceId, City, Location

---------------------------------------------------------------------
-- 20.4 有効なソリューション
---------------------------------------------------------------------

CREATE PROC ap_InventoryByMakeModel_Quick_TempTbl
-- 条件（指定の件数）を満たすレコード群を返す
-- 一時テーブルを使ってレコードセットを分割する例
    @Make VARCHAR(50) = '%',
    @Model VARCHAR(50) = '%',
    @FirstRec INT = 1,
    @LastRec INT = 25,
    @RowCount INT = NULL OUTPUT
/* テスト:
DECLARE @rc INT
EXEC ap_InventoryByMakeModel_Quick_TempTbl @RowCount = @rc output
SELECT @rc
EXEC ap_InventoryByMakeModel_Quick_TempTbl @FirstRec = 26,
                                           @LastRec = 50,
                                           @RowCount = @rc OUTPUT
*/
AS
SET NOCOUNT ON

CREATE TABLE #Inv(ID INT IDENTITY,
                  Inventoryid INT,
                  Make VARCHAR(50),
                  Model VARCHAR(50),
                  Location VARCHAR(50),
                  FirstName VARCHAR(30),
                  LastName VARCHAR(30),
                  AcquisitionType VARCHAR(12),
                  Address VARCHAR(50),
                  City VARCHAR(50),
                  ProvinceId CHAR(3),
                  Country VARCHAR(50),
                  EqType VARCHAR(50),
                  Phone VARCHAR(20),
                  Fax VARCHAR(20),
                  Email VARCHAR(128),
                  UserName VARCHAR(50)
)

INSERT INTO #Inv(InventoryId,     Make,      Model,
                 Location,        FirstName, LastName,
                 AcquisitionType, Address,   City,
                 ProvinceId,      Country,   EqType,
                 Phone,           Fax,       Email,
                 UserName
)
SELECT
    Inventory.Inventoryid,
    Equipment.Make,
    Equipment.Model,
    Location.Location,
    Contact.FirstName,
    Contact.LastName,
    AcquisitionType.AcquisitionType,
    Location.Address,
    Location.City,
    Location.ProvinceId,
    Location.Country,
    EqType.EqType,
    Contact.Phone,
    Contact.Fax,
    Contact.Email,
    Contact.UserName
FROM dbo.EqType EqType
RIGHT OUTER JOIN dbo.Equipment Equipment
    ON EqType.EqTypeId = Equipment.EqTypeId
RIGHT OUTER JOIN dbo.Inventory Inventory
    ON Equipment.EqId = Inventory.EqId
INNER JOIN dbo.Status Status
    ON Inventory.StatusId = Status.StatusId
LEFT OUTER JOIN dbo.AcquisitionType AcquisitionType
    ON Inventory.AcquisitionTypeID = AcquisitionType.AcquisitionTypeId
LEFT OUTER JOIN dbo.Location Location
    ON Inventory.LocationId = Location.LocationId
LEFT OUTER JOIN dbo.Contact Contact
    ON Inventory.OwnerId = Contact.ContactId
WHERE Make LIKE @Make
AND Model LIKE @Model
ORDER BY Location, LastName, FirstName

SELECT @RowCount = @@ROWCOUNT

SELECT *
FROM #Inv
WHERE ID >= @FirstRec AND ID <= @LastRec
ORDER BY ID
RETURN


DECLARE @rc INT
EXEC ap_InventoryByMakeModel_Quick_TempTbl @RowCount = @rc OUTPUT
SELECT @rc


DECLARE @rc INT
EXEC ap_InventoryByMakeModel_Quick_TempTbl @FirstRec = 26,
                                           @LastRec  = 50,
                                           @RowCount = @rc OUTPUT


CREATE PROC ap_InvSum_Generate
-- クエリ処理を高速化するための非正規化テーブルを生成する
    @debug INT = 0
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @intTransactionCountOnEntry INT

    CREATE TABLE #Inv(
        ID INT IDENTITY(1,1),
        Inventoryid INT,
        Make VARCHAR(50),
        Model VARCHAR(50),
        Location VARCHAR(50),
        Status VARCHAR(15),
        FirstName VARCHAR(30),
        LastName VARCHAR(30),
        AcquisitionType VARCHAR(12),
        Address VARCHAR(50),
        City VARCHAR(50),
        ProvinceId CHAR(3),
        Country VARCHAR(50),
        EqType VARCHAR(50),
        Phone VARCHAR(20),
        Fax VARCHAR(20),
        Email VARCHAR(128),
        UserName VARCHAR(50),
        MakeModelSIdx INT,
        LFNameSIdx INT,
        CountrySIdx INT
    )

    -- 結果セットを取得する
    INSERT INTO #Inv(
        InventoryId,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
    )
    SELECT
        Inventory.Inventoryid,
        Equipment.Make,
        Equipment.Model,
        Location.Location,
        Contact.FirstName,
        Contact.LastName,
        AcquisitionType.AcquisitionType,
        Location.Address,
        Location.City,
        Location.ProvinceId,
        Location.Country,
        EqType.EqType,
        Contact.Phone,
        Contact.Fax,
        Contact.Email,
        Contact.UserName
    FROM dbo.EqType EqType
    RIGHT OUTER JOIN dbo.Equipment Equipment
        ON EqType.EqTypeId = Equipment.EqTypeId
    RIGHT OUTER JOIN dbo.Inventory Inventory
        ON Equipment.EqId = Inventory.EqId
    LEFT OUTER JOIN dbo.AcquisitionType AcquisitionType
        ON Inventory.AcquisitionTypeID = AcquisitionType.AcquisitionTypeId
    LEFT OUTER JOIN dbo.Location Location
        ON Inventory.LocationId = Location.LocationId
    LEFT OUTER JOIN dbo.Contact Contact
        ON Inventory.OwnerId = Contact.ContactId
    ORDER BY Location, LastName, FirstName

    -- レコードの並べ替えを行う

    ---- Make、Model -------------------
    CREATE TABLE #tmp(SID INT IDENTITY(1, 1),
                      ID INT)

    INSERT INTO #tmp(ID)
    SELECT ID
    FROM #inv
    ORDER BY Make, Model

    UPDATE #inv
    SET MakeModelSIdx = #tmp.SId
    FROM #inv INNER JOIN #tmp
        ON #inv.ID = #tmp.id

    DROP TABLE #tmp

    ----------------------------------------
    ---- CountrySIdx：Country、Province、City、Location -------------------
    CREATE TABLE #tmp2(SID INT IDENTITY(1, 1),
                       ID INT)

    INSERT INTO #tmp2(ID)
    SELECT ID
    FROM #inv
    ORDER BY Country, ProvinceId, City, Location

    UPDATE #inv
    SET CountrySIdx = #tmp2.SId
    FROM #inv INNER JOIN #tmp2
    ON #inv.ID = #tmp2.id

    DROP TABLE #tmp2
    ----------------------------------------
    ---- LFNameSIdx：LName、FName -------------------
    CREATE TABLE #tmp3(SID INT IDENTITY(1, 1),
                       ID INT)

    INSERT INTO #tmp3(ID)
    SELECT ID
    FROM #inv
    ORDER BY LastName, FirstName

    UPDATE #inv
    SET LFNameSIdx = #tmp3.SId
    FROM #inv INNER JOIN #tmp3
        ON #inv.ID = #tmp3.id

    DROP TABLE #tmp3
    ----------------------------------------

    -- トランザクションを使用してユーザーからのアクセスをブロックする
    SELECT @intTransactionCountOnEntry = @@TRANCOUNT
    BEGIN TRANSACTION

    -- テーブルを再作成する
    IF EXISTS (SELECT * FROM dbo.sysobjects
               WHERE id = OBJECT_ID(N'[InventorySum]')
               AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
       DROP TABLE dbo.[InventorySum]

    CREATE TABLE dbo.InventorySum(
        ID INT,
        InventoryId INT,
        Make VARCHAR(50),
        Model VARCHAR(50),
        Location VARCHAR(50),
        FirstName VARCHAR(30),
        LastName VARCHAR(30),
        AcquisitionType VARCHAR(12),
        Address VARCHAR(50),
        City VARCHAR(50),
        ProvinceId CHAR(3),
        Country VARCHAR(50),
        EqType VARCHAR(50),
        Phone VARCHAR(20),
        Fax VARCHAR(20),
        Email VARCHAR(128),
        UserName VARCHAR(50),
        MakeModelSIdx INT,
        LFNameSIdx INT,
        CountrySIdx INT
    )

    -- テーブルにデータを取り込む
    INSERT INTO dbo.InventorySum(
        ID,
        InventoryId,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName,
        MakeModelSIdx,
        LFNameSIdx,
        CountrySIdx
    )
    SELECT
        ID,
        InventoryId,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName,
        MakeModelSIdx,
        LFNameSIdx,
        CountrySIdx
    FROM #inv

    -- インデックスを作成する
    CREATE UNIQUE CLUSTERED INDEX [idx_InvSum_Id]
    ON [dbo].[InventorySum] ([ID])

    CREATE INDEX [idx_InvSum_LFName]
    ON [dbo].[InventorySum] (LastName, FirstName)

    CREATE INDEX [idx_InvSum_Location]
    ON [dbo].[InventorySum] (Location)

    CREATE INDEX [idx_InvSum_ModelMakeEqType]
    ON [dbo].[InventorySum] (Model, Make, EqType)

    -- トランザクションを完了する - ユーザーにアクセスを許可する
    IF @@TRANCOUNT > @intTransactionCountOnEntry
        COMMIT TRANSACTION

    RETURN


CREATE PROC ap_InventoryByMakeModel_Quick1
    @Make VARCHAR(50) = NULL,  -- 条件
    @Model VARCHAR(50) = NULL  -- 条件
AS

SELECT
    Inventoryid,
    Make,
    Model,
    Location,
    FirstName,
    LastName,
    AcquisitionType,
    Address,
    City,
    ProvinceId,
    Country,
    EqType,
    Phone,
    Fax,
    Email,
    UserName
FROM dbo.InventorySum
WHERE Make LIKE @Make
AND Model LIKE @Model


EXEC ap_InventoryByMakeModel_Quick1 'Compaq', 'D%'


CREATE PROC ap_InventoryByMakeModel_Quick2
    @Make VARCHAR(50) = NULL,  -- 条件
    @Model VARCHAR(50) = NULL,  -- 条件
    @SortOrderId SMALLINT = 0
AS

    SELECT
        Id = CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END,
        Inventoryid,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
        FROM dbo.InventorySum
        WHERE Make LIKE @Make
        AND Model LIKE @Model
        ORDER BY CASE @SortOrderId
                     WHEN 1 THEN MakeModelSIdx
                     WHEN 2 THEN CountrySIdx
                     WHEN 3 THEN LFNameSIdx
                 END
    RETURN


EXEC ap_InventoryByMakeModel_Quick2 'Compaq', 'D%', 1


CREATE PROC ap_InventoryByMakeModel_Quick3
    @Make VARCHAR(50) = NULL,   -- 条件
    @Model VARCHAR(50) = NULL,  -- 条件
    @PreviousID INT = 0,        -- 前のバッチの最後のレコード
    @SortOrderId SMALLINT = 0
AS

    SELECT TOP 25
        Id = CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END,
        Inventoryid,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
    FROM dbo.InventorySum
    WHERE CASE @SortOrderId
              WHEN 1 THEN MakeModelSIdx
              WHEN 2 THEN CountrySIdx
              WHEN 3 THEN LFNameSIdx
          END > @PreviousID
    AND Make LIKE @Make
    AND Model LIKE @Model
    ORDER BY CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END
    RETURN


EXEC ap_InventoryByMakeModel_Quick3 'Compaq', 'D%', 444, 1


CREATE PROC ap_InventoryByMakeModel_Quick
    @Make VARCHAR(50) = NULL,   -- 条件
    @Model VARCHAR(50) = NULL,  -- 条件
    @PreviousID INT = 0,        -- 前のバッチの最後のレコード
    @SortOrderId SMALLINT = 0,
    @SearchTypeid TINYINT = 0   -- 0：指定した語句で始まる
                                -- 1：指定した語句と完全に一致する
                                -- 2：指定した語句を含む
AS

    IF @SearchTypeId = 0
    BEGIN
        SET @Make = @Make + '%'
        SET @Model = @Model + '%'
    END

    IF @SearchTypeid = 2
    BEGIN
        SET @Make  = '%' + @Make  + '%'
        SET @Model = '%' + @Model + '%'
    END

    SELECT TOP 25
        Id = CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END,
        Inventoryid,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
    FROM dbo.InventorySum
    WHERE CASE @SortOrderId
              WHEN 1 THEN MakeModelSIdx
              WHEN 2 THEN CountrySIdx
              WHEN 3 THEN LFNameSIdx
          END > @PreviousID
    AND Make LIKE @Make
    AND Model LIKE @Model
    ORDER BY CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END
    RETURN


EXEC ap_InventoryByMakeModel_Quick 'Compaq', 'D', 50, 2, 2


CREATE PROC ap_InventoryByMakeModel_Count
    @Make VARCHAR(50) = NULL,   -- 条件
    @Model VARCHAR(50) = NULL,  -- 条件
    @SearchTypeid TINYINT = 0,  -- 0：指定した語句で始まる
                                -- 1：指定した語句と完全に一致する
                                -- 2：指定した語句を含む
    @Count INT OUTPUT
AS

    IF @SearchTypeId = 0
    BEGIN
        SET @Make = @Make + '%'
        SET @Model = @Model + '%'
    END

    IF @SearchTypeid = 2
    BEGIN
        SET @Make  = '%' + @Make  + '%'
        SET @Model = '%' + @Model + '%'
    END

    SELECT @Count = COUNT(*)
    FROM dbo.InventorySum
    WHERE Make LIKE @Make
    AND Model LIKE @Model

    RETURN


DECLARE @count INT
EXEC ap_InventoryByMakeModel_Count 'Compaq', 'D', 2, @count OUTPUT
SELECT @count count


CREATE PROC ap_InventorySearchAdvFull_ListPage
-- 条件を満たす25件の備品を表示する

-- 動的に構築するクエリと、代理インデックス列を
-- 含む非正規化されたテーブルを使用して、結果を25レコードの
-- バッチで返すストアドプロシージャの例
    @Make VARCHAR(50) = NULL,
    @Model VARCHAR(50) = NULL,
    @Location VARCHAR(50) = NULL,
    @FirstName VARCHAR(30) = NULL,
    @LastName VARCHAR(30) = NULL,
    @AcquisitionType VARCHAR(20) = NULL,
    @ProvinceId CHAR(3) = NULL,
    @Country VARCHAR(50) = NULL,
    @EqType VARCHAR(30) = NULL,
    @City VARCHAR(50) = NULL,
    @UserName VARCHAR(50) = NULL,
    @email VARCHAR(50) = NULL,
    @SortOrderId SMALLINT = 0,  -- 1: Make（メーカー）、Model（モデル）
                                -- 2: Country（国）、Prov（州）、City（市）、Loc（場所）
                                -- 4: LName（姓）、FName（名）
    @PreviousID INT = 0,        -- 前のバッチの最後のレコード
    @BatchSize INT = 25,
    @debug INT = 0
AS

    SET CONCAT_NULL_YIELDS_NULL OFF
    SET NOCOUNT ON

    DECLARE @chvSelect VARCHAR(max),
            @chvFrom VARCHAR(max),
            @chvWhere VARCHAR(max),
            @chvOrderby VARCHAR(max),
            @chvSQL VARCHAR(max)

    -- レコードを順序付ける
    SET @chvSelect = 'SELECT TOP ' + CONVERT(VARCHAR, @BatchSize)
                   + '    Inventoryid, Make,            Model,
                          Location,    FirstName,
                          LastName,    AcquisitionType, Address,
                          City,        ProvinceId,      Country,
                          EqType,      Phone,           Fax,
                          Email,       UserName, '
                   +      CASE @SortOrderId
                              WHEN 1 THEN ' MakeModelSIdx '
                              WHEN 2 THEN ' CountrySIdx '
                              WHEN 3 THEN ' LFNameSIdx '
                          END
                   + '    AS ID '

    SET @chvFrom = ' FROM dbo.InventorySum '

    SET @chvWhere = ' WHERE '
                  + CASE @SortOrderId
                        WHEN 1 THEN ' MakeModelSIdx '
                        WHEN 2 THEN ' CountrySIdx '
                        WHEN 3 THEN ' LFNameSIdx '
                    END + '> '
                  + CONVERT(VARCHAR, @PreviousID)

    IF @Make IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Make = ''' + @Make + ''' '
    IF @Model IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Model = ''' + @Model + ''' '
    IF @Location IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Location = ''' + @Location + ''' '
    IF @FirstName IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND FirstName = ''' + @FirstName + ''' '
    IF @LastName IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND lastName = ''' + @lastName + ''' '
    IF @AcquisitionType IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND AcquisitionType = '''
                      + @AcquisitionType + ''' '
    IF @ProvinceId  IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND ProvinceId = ''' + @ProvinceId + ''' '
    IF @Country IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Country = ''' + @Country + ''' '
    IF @EqType IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND EqType = ''' + @EqType + ''' '
    IF @City IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND City = ''' + @City + ''' '
    IF @UserName IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND UserName = ''' + @UserName + ''' '
    IF @email IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND email = ''' + @email + ''' '

    SET @chvOrderBy = ' ORDER BY '
                    + CASE @SortOrderId
                          WHEN 1 THEN ' MakeModelSIdx'
                          WHEN 2 THEN ' CountrySIdx '
                          WHEN 3 THEN ' LFNameSIdx '
                      END

    SET @chvSQL = @chvSelect + @chvFrom + @chvWhere + @chvOrderby

    IF @debug = 0
        EXEC (@chvSQL)
    ELSE
        SELECT @chvSQL


EXEC ap_InventorySearchAdvFull_ListPage
    @Make = 'Compaq',
    @Model= NULL,
    @Location = NULL,
    @FirstName = 'Michael',
    @LastName = NULL,
    @AcquisitionType = NULL,
    @ProvinceId  = NULL,
    @Country = NULL,
    @EqType = NULL,
    @City = NULL,
    @UserName = NULL,
    @email = NULL,
    @SortOrderId = 1,   -- 1: メーカーとモデルで並び替え
    @PreviousID = 25,   -- 前のバッチの最後のレコード
    @BatchSize = 25,
    @debug = 0


ALTER DATABASE Asset5
    SET ALLOW_SNAPSHOT_ISOLATION ON;


ALTER DATABASE Asset5
    SET READ_COMMITTED_SNAPSHOT ON;


SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
EXEC ap_InventoryByMakeModel_Quick 'Nec', 'V', 50, 2, 2


SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
DECLARE @EqId INT

SELECT @EqId = EqId
FROM dbo.Inventory
WHERE InventoryID = 117

BEGIN TRAN
UPDATE dbo.Eq
SET Model = 'V91'
WHERE EqId = @EqId
UPDATE dbo.Eq
SET Model = 'V90'
WHERE EqId = (SELECT EqId
              FROM Inventory
              WHERE InventoryID = 118)

COMMIT TRAN


SqlConnection conn = new (string myConnString);
...
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Snapshot);
...


SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT;
GO
EXEC ap_InventoryByMakeModel_Quick 'Nec', 'V', 50, 2, 2

