---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第3章 Transact-SQLのプログラミング構造の基礎
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 3.2 データベースオブジェクトの修飾子
---------------------------------------------------------------------

SQLBox.Asset5.dbo.ap_Eq_List


ap_Eq_List
dbo.ap_Eq_List
Asset5.dbo.ap_Eq_List
Asset5..ap_Eq_List
SQLBox.Asset5..ap_Eq_List
SQLBox...ap_Eq_List
SQLBox..dbo.ap_Eq_List

---------------------------------------------------------------------
-- 3.3 データ型
---------------------------------------------------------------------

CREATE TABLE Contacts(
    ContactId CHAR(8),
    Name VARCHAR(50),
    Note TEXT,
    Resume VARCHAR(max)
)


INSERT INTO Contacts(ContactId, Name, Note, Resume)
VALUES('CO-92-81', 'Tom Jones', 'Tom@trigon.com', 'N/A')


CREATE TABLE Contacts_2(
    ContactId NCHAR(8),
    Name NVARCHAR(50),
    Note NTEXT,
    Resume NVARCHAR(max)
)


INSERT INTO Contacts_2(ContactId, Name, Note, Resume)
VALUES(N'CO-92-81', N'Tom Jones', N'Tom@trigonblue.com', N'N/A')


CREATE TABLE Contacts_3(
    ContactId NCHAR(8),
    Name NVARCHAR(50),
    DateOfBirth SMALLDATETIME,
    Resume NVARCHAR(max)
)

INSERT INTO Contacts_3(ContactId, Name, DateOfBirth, Resume)
VALUES(N'CO-92-81', N'Tom Jones', '1965/2/21 10:03 AM', N'Tom@trigonblue.com')


SELECT *
FROM Contacts_3
WHERE DateOfBirth = '1965/2/21'


SELECT *
FROM Contacts_3
WHERE DateOfBirth >= '1965/2/21'
AND DateOfBirth < '1965/2/22'


UPDATE Inventory
SET StatusId = 3
WHERE InventoryId = 1042


CREATE TABLE Patient(
    PatientId INT,
    FullName VARCHAR(30),
    Weight DECIMAL(5, 2),
    Height DECIMAL(5, 2),
    ADP SMALLINT,
    BDZ TINYINT
)


INSERT INTO Patient(PatientId, FullName, Weight, Height, ADP, BDZ)
VALUES(834021, 'Tom Jones', 89.5, 188.5, 450, 11)


UPDATE Inventory
SET Rent = $0
WHERE InventoryId = 1042


CREATE TABLE MyTable(
    Id INT,
    BinData VARBINARY(8000),
    Diagram VARBINARY(max)
)


UPDATE MyTable
SET BinData = 0x82A7210B
WHERE Id = 121131


UPDATE dbo.MyTable
SET Diagram =
(
    SELECT *
    FROM OPENROWSET
    (
        BULK 'C:\My Pictures\desktop.bmp', SINGLE_BLOB
    ) AS a
)
WHERE Id = 121131


CREATE TABLE dbo.Eq2(
    EqId INT,
    EqCompList XML
)


INSERT INTO dbo.Eq2(EqId, EqCompList)
VALUES(123, '<CompList><CZ101/><AZ401/><BZ407/></CompList>')


CREATE TABLE Lookup(
    LookupGroupId TINYINT,
    LookupId SMALLINT,
    LookupValue SQL_VARIANT
)


INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(2, 34, 'VAR')

INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(3, 22, 2000)

INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(4, 16, '1/12/2000')

INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(4, 11, $50000)


CREATE TYPE Phone FROM VARCHAR(20) NOT NULL
CREATE TYPE typPostalCode FROM VARCHAR(7) NULL

---------------------------------------------------------------------
-- 3.4 変数
---------------------------------------------------------------------

DECLARE @LastName VARCHAR(50)


DECLARE @LastName VARCHAR(50),
        @FirstName VARCHAR(30),
        @BirthDate SMALLDATETIME


DECLARE @OfficePhone phone


SELECT @LastName = 'Smith'


SELECT @LastName = 'Smith',
       @FirstName = 'David',
       @BirthDate = '2/21/1965'


SELECT @Make = Equipment.Make,
       @Model = Equipment.Model,
       @EqType = EqType.EqType
FROM EqType INNER JOIN Equipment
    ON EqType.EqTypeId = Equipment.EqTypeId
WHERE Equipment.EqId = 2


DECLARE @make VARCHAR(50),
        @model VARCHAR(50),
        @EqType VARCHAR(50)

SELECT @Make = 'ACME',
       @Model = 'Turbo',
       @EqType = 'cabadaster'

SELECT @Make = make,
       @Model = Model,
       @EqType = EqType.EqType
FROM EqType INNER JOIN Equipment
    ON EqType.EqTypeId = Equipment.EqTypeId
WHERE EqId = -1

SELECT @make make, @model model, @EqType EqType


SELECT @Make = Make,
       @Model = Model,
       @EquipmentName = Make + ' ' + Model,
       @EqType = (SELECT EqType
                  FROM dbo.EqType EqType
                  WHERE EqType.EqTypeId = Equipment.EqTypeId)
FROM dbo.Equipment
WHERE EqId = 2


SELECT Make,
       @Model = Model -- これは誤り
FROM dbo.Equipment
WHERE EqId = 2


SET @LastName = 'Johnson'


SET @Make = 'ACME'
SET @Model = 'Turbo'
SET @EqType = 'cabadaster'


UPDATE Inventory
SET @mnsCost = Cost = Cost * @fltTaxRate
WHERE InventoryId = @intInventoryId


SELECT @LastName
PRINT @FirstName


SELECT Make "Selected Make",
       Model "Selected Model",
       @Model "Original Model"
FROM Equipment
WHERE EqId = 2


CREATE TABLE Eq(
    EqId INT IDENTITY(1, 1),
    Make VARCHAR(50),
    Model VARCHAR(50),
    EqTypeId INT
)


DECLARE @intEqId INT
INSERT INTO Eq(Make, Model, EqTypeId)
VALUES ('ACME', 'Turbo', 2)
SET @intEqId = @@IDENTITY


DECLARE @intEqId INT
INSERT INTO Eq(Make, Model, EqTypeId)
    SELECT Make, Model, EqTypeID
    FROM Equipment
SELECT @intEqId = @@IDENTITY


UPDATE Equipment
SET EqTypeId = 3
WHERE EqTypeId = 2
SELECT @intErrorCode = @@ERROR


DECLARE @intEqId INT,
        @intErrorCode INT
INSERT INTO Equipment(Make, Model, EqTypeId)
VALUES('ACME', 'Turbo', 2)
SELECT @intEqId = @@IDENTITY,
       @intErrorCode = @@ERROR


SELECT Make, Model, EqTypeid
INTO OldEquipment
FROM Equipment
WHERE EqTypeid = 2

IF @@ROWCOUNT = 0
    PRINT N'行はコピーされませんでした'


DECLARE @MyTableVar TABLE
(
    Id INT PRIMARY KEY,
    Lookup VARCHAR(15)
)

INSERT @MyTableVar VALUES(1, '1Q2000')
INSERT @MyTableVar VALUES(2, '2Q2000')
INSERT @MyTableVar VALUES(3, '3Q2000')
SELECT * FROM @MyTableVar


SELECT LookupId, Lookup
INTO @TableVariable -- これは誤り
FROM Lookup


INSERT INTO @TableVariable -- これは誤り
    EXEC prMyProcedure

---------------------------------------------------------------------
-- 3.5 フロー制御ステートメント
---------------------------------------------------------------------

SELECT LeaseId, LeaseVendor --, LeaseNumber
FROM dbo.Lease
WHERE ContractDate > '1/1/1999'


-- SELECT * FROM dbo.Equipment -- デバッグのためのコメントアウト


/*
この行も次の行もコメント
実行時にはすべて無視される
*/

/* すべての備品を表示 */
SELECT * FROM Equipment


/*
-- すべての備品を表示
SELECT * FROM Equipment
*/


/* この行はコメント。
/* SQL Server 2000のクエリアナライザは、次の区切りを最初のコメントの終わりと
解釈するが、SQL Server 2005のSQL Server Management Studioでは
正しく動作する */
   場合によっては、これで構文エラーが発生する */
SELECT * FROM dbo.Equipment


/*****************************************************************
** ファイル： ap_Equipment_Insert.sql
** 名前： ap_Equipment_Insert
** 説明： 備品とその形式を挿入
**       （存在しない場合のみ）
**
** 戻り値： ErrorCode
**
** 呼び出し元：   middleware
**
** パラメータ：
** Input                                  Output
** ----------                             -----------
** Make                                   EqId
** Model
** EqType
**
** 作者： Dejan Sunderic
** 作成日： 1/1/2005
******************************************************************
** 修正履歴
******************************************************************
** 日付：       作業者：     作業内容
** --------     --------     -------------------------------------
** 11/1/2005    DS           Fixed:49. エラー処理を修正
** 11/2/2005    DS           Fixed:36. パフォーマンスを最適化
*****************************************************************/


BEGIN
    INSERT [Order](OrderDate, RequestedById, TargetDate, DestinationLocation)
    VALUES(@OrderDate, @ContactId, @TargetDate, @LocId)
    SELECT @ErrorCode = @@ERROR

    IF @ErrorCode <> 0
    BEGIN
        RAISERROR(N'注文の挿入中にエラーが発生しました', 16, 1)
    END
END


IF @IntErrorCode= 0
BEGIN
    INSERT [Order](OrderDate, RequestedById, TargetDate, DestinationLocation)
    VALUES(@OrderDate, @ContactId, @TargetDate, @LocId)
    SELECT @ErrorCode = @@ERROR
    @intOrderId = @@IDENTITY
END


CREATE PROCEDURE dbo.ap_Equipment_Insert_1
-- Equipmentテーブルに値を格納する
-- 呼び出し元にレコードのIDを返す
(
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30)
)
AS
    DECLARE @intEqTypeId INT,
            @intEqId INT
    -- EqTypeのIDを取得する
    SELECT @intEqTypeId = EqTypeId
    FROM dbo.EqType
    WHERE EqType = @chvEqType

    -- データベースにEqTypeが存在するかどうかを調べる
    IF @intEqTypeId IS NOT NULL
        -- 備品のレコードを挿入する
        INSERT dbo.Equipment(Make, Model, EqTypeId)
        VALUES(@chvMake, @chvModel, @intEqTypeId)
    ELSE
        -- EqTypeが存在しない場合、以下を実行する
        BEGIN
            -- 新しいEqTypeをデータベースに追加する
            INSERT dbo.EqType(EqType)
            VALUES(@chvEqType)
            -- 追加したレコードのIDを取得する
            SELECT @intEqTypeId = @@IDENTITY
            -- 備品のレコードを挿入する
            INSERT dbo.Equipment(Make, Model, EqTypeId)
            VALUES(@chvMake, @chvModel, @intEqTypeId)
       END
    SELECT @intEqId = @@IDENTITY
    -- 呼び出し元にIDを返す
    RETURN @intEqId


CREATE PROCEDURE dbo.ap_Equipment_Insert_2
-- Equipmentテーブルに値を格納する
-- 呼び出し元にレコードのIDを返す
(
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30)
)
AS
    DECLARE @intEqTypeId INT,
            @intEqId INT

    -- データベースにEqTypeが存在するかどうかを調べる
    IF NOT EXISTS (SELECT EqTypeId FROM dbo.EqType WHERE EqType = @chvEqType)
        -- EqTypeが存在しない場合、以下を実行する
        BEGIN
            -- 新しいEqTypeをデータベースに追加する
            INSERT dbo.EqType(EqType)
            VALUES(@chvEqType)
            -- 追加したレコードのIDを取得する
            SELECT @intEqTypeId = @@IDENTITY
        END
    ELSE
        -- EqTypeのIDを取得する
        SELECT @intEqTypeId = EqTypeId
        FROM dbo.EqType
        WHERE EqType = @chvEqType

    -- 備品のレコードを挿入する
    INSERT dbo.Equipment(Make, Model, EqTypeId)
    VALUES(@chvMake, @chvModel, @intEqTypeId)

    SELECT @intEqId = @@IDENTITY
    -- 呼び出し元にIDを返す
    RETURN @intEqId


CREATE PROCEDURE dbo.ap_Equipment_Insert_3
-- Equipmentテーブルに値を格納する
-- 呼び出し元にレコードのIDを返す
(
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30)
)
AS
    DECLARE @intEqTypeId INT,
            @ErrorCode INT,
            @intEqId INT

    -- データベースにEqTypeが存在するかどうかを調べる
    IF NOT EXISTS (SELECT EqTypeId FROM dbo.EqType WHERE EqType = @chvEqType)
        -- EqTypeが存在しない場合、以下を実行する
        BEGIN
            -- 新しいEqTypeをデータベースに追加する
            INSERT EqType(EqType)
            VALUES(@chvEqType)
            -- 追加したレコードのIDを取得する
            SELECT @intEqTypeId = @@IDENTITY,
                   @ErrorCode = @@ERROR
            IF @ErrorCode <> 0
                BEGIN
                    SELECT N'備品型式を追加できませんでした。エラー：', @ErrorCode
                    RETURN -1
                END
        END
    ELSE
        -- EqTypeのIDを取得する
        SELECT @intEqTypeId = EqTypeId
        FROM dbo.EqType
        WHERE EqType = @chvEqType

    -- 備品のレコードを挿入する
    INSERT Equipment(Make, Model, EqTypeId)
    VALUES(@chvMake, @chvModel, @intEqTypeId)
    -- 呼び出し元にIDを返す
    SELECT @intEqId = @@IDENTITY,
           @ErrorCode = @@ERROR
    IF @ErrorCode <> 0
        BEGIN
            SELECT N'備品を挿入できませんでした。エラー：', @ErrorCode
            RETURN -2
        END
    RETURN @intEqId


CREATE PROCEDURE ap_CalcFactorial
-- 階乗を計算する
-- 1! = 1
-- 3! = 3 * 2 * 1
-- n! = n * (n-1)* ... 5 * 4 * 3 * 2 * 1
    @inyN TINYINT,
    @intFactorial BIGINT OUTPUT
AS
    SET @intFactorial = 1
    WHILE @inyN > 1
    BEGIN
         SET @intFactorial = @intFactorial * @inyN
         SET @inyN = @inyN - 1
    END
    RETURN 0


CREATE PROCEDURE ap_InventoryProperties_Get
/************************************************************
備品のプロパティを、次のようにセミコロンで区切ったリストを返す
; Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

テスト方法:
DECLARE @p VARCHAR(max)
EXEC ap_InventoryProperties_Get 5, @p OUTPUT
SELECT @p
*************************************************************/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT
)
AS
    DECLARE @intCountProperties INT,
            @intCounter INT,
            @chvProperty VARCHAR(50),
            @chvValue VARCHAR(50),
            @chvUnit VARCHAR(50)

    CREATE TABLE #Properties(
        Id INT IDENTITY(1, 1),
        Property VARCHAR(50),
        Value VARCHAR(50),
        Unit VARCHAR(50)
    )

    -- 備品のプロパティを特定する
    INSERT INTO #Properties(Property, Value, Unit)
        SELECT Property, Value, Unit
        FROM dbo.InventoryProperty InventoryProperty
        INNER JOIN dbo.Property Property
            ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    -- ループを設定する
    SELECT @intCountProperties = COUNT(*),
           @intCounter = 1,
           @chvProperties = ''
    FROM #Properties

    -- プロパティリスト全体をループする
    WHILE @intCounter <= @intCountProperties
    BEGIN
        -- 各プロパティの値を取得する
        SELECT @chvProperty = Property,
               @chvValue = Value,
               @chvUnit = Unit
        FROM #Properties
        WHERE Id = @intCounter

        -- リストを作成する
        SET @chvProperties = @chvProperties + '; '
                           + @chvProperty + '='
                           + @chvValue + ' '
                           + ISNULL(@chvUnit, '')

        -- 前に戻って次の備品を取得する
        SET @intCounter = @intCounter + 1
    END
    IF SUBSTRING(@chvProperties, 0, 2) = '; '
        SET @chvProperties = RIGHT(@chvProperties, LEN(@chvProperties) - 2)

    DROP TABLE #Properties
    RETURN 0


CREATE PROCEDURE dbo.ap_Lease_Close
-- 指定のリース番号に対応する備品のリース情報を削除する
    @intLeaseId INT
AS
    -- リース情報を更新する
    UPDATE dbo.Inventory
    SET Rent = 0,
        LeaseId = NULL,
        LeaseScheduleId = NULL
    WHERE LeaseId = @intLeaseId
    IF @@ERROR <> 0 GOTO PROBLEM_1

    -- スケジュールを削除する
    DELETE FROM dbo.LeaseSchedule
    WHERE LeaseId = @intLeaseId
    IF @@ERROR <> 0 GOTO PROBLEM_2

    -- リースを削除する
    DELETE FROM dbo.Lease
    WHERE LeaseId = @intLeaseId
    IF @@ERROR <> 0 GOTO PROBLEM_3
    RETURN 0

    PROBLEM_1:
        SELECT N'リース情報を更新できません'
        RETURN 50001
    PROBLEM_2:
        SELECT N'データベースからスケジュールを削除できません'
        RETURN 50002
    PROBLEM_3:
        SELECT N'データベースからリースを削除できません'
        RETURN 50003


WAITFOR DELAY '00:01:00'
    SELECT * FROM Equipment


WAITFOR TIME '23:00'
    BACKUP DATABASE Asset5 TO Asset_bkp


---------------------------------------------------------------------
-- 3.6 カーソル
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_InventoryProperties_Get_Cursor
/********************************************************************
備品のプロパティを、次のようにセミコロンで区切ったリストを返す
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

テスト:
DECLARE @p VARCHAR(max)
EXEC dbo.ap_InventoryProperties_Get_Cursor 5, @p OUTPUT
SELECT @p
********************************************************************/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT
)
    AS
    DECLARE @intCountProperties INT,
            @intCounter INT,
            @chvProperty VARCHAR(50),
            @chvValue VARCHAR(50),
            @chvUnit VARCHAR(50)

    SET @chvProperties = ''

    DECLARE @CrsrVar CURSOR

    SET @CrsrVar = CURSOR FOR
        SELECT Property, Value, Unit
        FROM dbo.InventoryProperty InventoryProperty
        INNER JOIN dbo.Property Property
            ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    OPEN @CrsrVar

    FETCH NEXT FROM @CrsrVar
    INTO @chvProperty, @chvValue, @chvUnit

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @chvUnit = COALESCE(@chvUnit, '')
        -- リストを作成する
        SET @chvProperties = @chvProperties + @chvProperty + '='
                            + @chvValue + ' ' +  @chvUnit + '; '
        FETCH NEXT FROM @CrsrVar
        INTO @chvProperty, @chvValue, @chvUnit
    END

    CLOSE @CrsrVar
    DEALLOCATE @CrsrVar
    RETURN 0


CREATE PROCEDURE util.ap_Tables_BcpOut
AS
    DECLARE @chvTable VARCHAR(128),
            @chvCommand VARCHAR(255)

    DECLARE @curTables CURSOR

    -- カレントデータベースからすべてのユーザー定義テーブルを取得する
    SET @curTables = CURSOR FOR
        SELECT name
        FROM sysobjects
        WHERE xType = 'U'

    OPEN @curTables

    -- 最初のテーブルを取得する
    FETCH NEXT FROM @curTables
    INTO @chvTable

    -- カレントレコードの読み取りに成功したら、次の処理を行う
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- テーブルをエクスポートするためのDOSコマンドを生成する
        SET @chvCommand = 'bcp Asset5.dbo.' + @chvTable
                        + ' out C:\backup\' + @chvTable
                        + '.txt -c -q -Slg -Usa -Pdejan'

        -- コマンドラインユーティリティを実行し、テーブルをエクスポートする
        EXECUTE master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT

        FETCH NEXT FROM @curTables
        INTO @chvTable
    END

    CLOSE @curTables
    DEALLOCATE @curTables
    RETURN 0

