---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第16章 デバッグ
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 16.4 CLR データベースオブジェクトのデバッグ
---------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cp_DebugTest()
    {
        int count = 1;
        SqlContext.Pipe.Send("Hello world 1!\n");
        count++;
        SqlContext.Pipe.Send("Hello world 2!\n");

        using (SqlConnection conn =
            new SqlConnection("Context Connection=true"))
        {
            string EqType = "";

            // ストアドプロシージャの実行に使用するコマンドオブジェクトを設定
            SqlCommand cmd = new SqlCommand("dbo.ap_EqType_List", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            // ストアドプロシージャを実行
            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read()) // 次の行がなくなるまで行を進める
                {
                    // データストリームから戻された出力パラメータを返す
                    //id = reader.GetInt32(0); // 最初の列は無視
                    EqType = EqType + reader.GetString(1) + ", ";
                }
            }
        }
    }
};


EXEC dbo.cp_DebugTest

---------------------------------------------------------------------
-- 16.5 デバッガの代用
---------------------------------------------------------------------

@debug INT = 0


IF @debug <> 0
     SELECT @chvProperty Property,
            @chvValue Value,
            @chvUnit Unit

IF @debug <> 0
    SELECT * FROM #Properties


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

テスト方法：
DECLARE @p VARCHAR(max)
EXEC ap_InventoryProperties_Get 5, @p OUTPUT, 1
SELECT @p
*************************************************************/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT,
    @debug INT = 0
)
AS
    DECLARE @intCountProperties INT,
            @intCounter INT,
            @chvProperty VARCHAR(50),
            @chvValue VARCHAR(50),
            @chvUnit VARCHAR(50),
            @chvProcedure SYSNAME

    SET @chvProcedure = 'ap_InventoryProperties_Get'

    IF @debug <> 0
        SELECT '**** ' + @chvProcedure + 'START ****'

    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

    IF @debug = 1
        SELECT * FROM #Properties

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

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

        IF @debug <> 0
            SELECT @chvProperty Property,
                   @chvValue Value,
                   @chvUnit Unit

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

        IF @debug = 1
            SELECT @chvProperties [@chvProperties],
                   @intCounter [@intCounter]

        -- 前に戻って次の備品を取得する
        SET @intCounter = @intCounter + 1
    END

    IF SUBSTRING(@chvProperties, 0, 2) = '; '
        SET @chvProperties = RIGHT(@chvProperties, LEN(@chvProperties) - 2)

    DROP TABLE #Properties

    IF @debug <> 0
        SELECT '**** ' + @chvProcedure + 'END ****'

    RETURN 0


DECLARE @chvResult VARCHAR(max)
EXEC dbo.ap_InventoryProperties_Get
    @intInventoryId = 5,
    @chvProperties = @chvResult OUTPUT,
    @debug = 1

SELECT @chvResult Result


EXEC dbo.ap_InventoryProperties_Get
          @intInventoryId = 5,
          @chvProperties = @chvResult OUTPUT


DECLARE @chvProcedure SYSNAME
SET @chvProcedure = 'ap_InventoryProperties_Get'

IF @debug <> 0
    SELECT '**** '+ @chvProcedure + 'START ****'
...

IF @debug <> 0
    SELECT '**** '+ @chvProcedure + 'END ****'

RETURN 0


EXEC dbo.ap_InventoryProperties_Get
    @intInventoryId,
    @chvProperties OUTPUT,
    @debug


UPDATE dbo.EquipmentBC
SET EqBC = '111'
    OUTPUT deleted.*
WHERE EqID = 1


INSERT dbo.Equipment2(EqTypeID, Make, Model)
    OUTPUT inserted.EqId, inserted.EqTypeID,
           inserted.Make + ' - ' + inserted.Model
VALUES (22, 'Alfa', '2000X');


DECLARE @tbl TABLE(EqId INT,
                   EqTypeID INT,
                   Eq VARCHAR(100));

DELETE dbo.Equipment2
    OUTPUT deleted.EqId,
           deleted.EqTypeID,
           deleted.Make + ' - ' + deleted.Model
    INTO @tbl
WHERE Make LIKE 'A%';

SELECT t.*, EqType.EqType
FROM @tbl t INNER JOIN EqType
ON t.EqTypeID = EqType.EqTypeID

---------------------------------------------------------------------
-- 16.7 典型的なエラー
---------------------------------------------------------------------

DECLARE @intRowCount INT
DECLARE @intErrorCode INT

UPDATE Inventory
SET StatusId = 3
WHERE InventoryID = -11

SELECT @intRowCount = @@ROWCOUNT,
       @intErrorCode = @@ERROR

IF @intRowCount = 0
    SELECT N'レコードが更新されませんでした!'


CONVERT(VARCHAR, @intPropertyId)


DECLARE @test VARCHAR
SET @test = '123456789012345678901234567890'
SELECT DATALENGTH(@test), @test


/************************************************************
**     SELECT *
**     FROM #Properties
*************************************************************/


CREATE PROCEDURE dbo.ap_DeferredNameResolution
AS
    SET NOCOUNT ON
    SELECT 'Start'
    SELECT * FROM NonExistingTable
    SELECT 'Will execution be stopped?'
    RETURN

