プリザンターの内部CRUD操作をSQLだけで実現してみる[第4回：Results・Issuesテーブル編]

Posted at 2026-04-17

はじめに

前回は Users テーブルの CRUD 操作を紹介しました。第 4 回では、プリザンターのデータテーブルの中核である Results（記録テーブル）と Issues（期限付きテーブル）の CRUD 操作を見ていきます。

これらのテーブルは、マスタテーブルとは異なり Sites・Items テーブルとの連携が必要になります。また、ClassA 〜 ClassZ などの拡張カラムも持つため、少し複雑な構造をしています。

回	テーマ
第 1 回	概要と Depts テーブル編 — 共通設計と組織テーブルの CRUD
第 2 回	Groups テーブル編 — グループとメンバー管理の CRUD
第 3 回	Users テーブル編 — ユーザー管理の CRUD
第 4 回（本記事）	Results・Issues テーブル編 — レコードデータの CRUD
第 5 回	Wikis テーブル編 — Wiki の CRUD

本連載の SQL は SQL Server を対象にしています。PostgreSQL や MySQL を使用している場合は、関数名やデータ型を適宜読み替えてください。

データテーブルの全体像

プリザンターのデータテーブルは Sites・Items テーブルと連携して動作します。まずはこの関係を理解しましょう。

テーブル	役割
`Sites`	サイト（テーブル）の定義情報を管理。`ReferenceType` で記録テーブルか期限付きテーブルかを区別する
`Items`	全データレコードのインデックス。全文検索の `FullText` カラムや表示用 `Title` を保持する
`Results` / `Issues`	実際のデータを格納するテーブル

レコードを作成する際は Results（または Issues）と Items の両方にレコードを追加する必要があります。

Results テーブル（記録テーブル）

テーブル構造（主要カラム）

カラム	型	NULL	説明
`SiteId`	bigint	NO	サイト ID
`ResultId`	bigint	NO	レコード ID（主キー、IDENTITY）
`Ver`	int	NO	バージョン番号
`Title`	nvarchar(1024)	YES	タイトル
`Body`	nvarchar(max)	YES	内容
`Status`	int	YES	ステータス
`Manager`	int	YES	管理者 UserId
`Owner`	int	YES	担当者 UserId
`Locked`	bit	YES	レコードロックフラグ
`Comments`	nvarchar(max)	YES	コメント（JSON）
`Creator`	int	NO	作成者 UserId
`Updator`	int	NO	更新者 UserId
`CreatedTime`	datetime	NO	作成日時
`UpdatedTime`	datetime	NO	更新日時

拡張カラム

Results テーブルと Issues テーブルには以下の拡張カラムが存在します。各カテゴリ A 〜 Z の 26 カラムずつ、合計 156 カラムがあります。

カテゴリ	型	用途
`ClassA` 〜 `ClassZ`	nvarchar(1024)	分類（ドロップダウンなど）
`NumA` 〜 `NumZ`	decimal	数値
`DateA` 〜 `DateZ`	datetime	日付
`DescriptionA` 〜 `DescriptionZ`	nvarchar(max)	説明（長文テキスト）
`CheckA` 〜 `CheckZ`	bit	チェック（真偽値）
`AttachmentsA` 〜 `AttachmentsZ`	nvarchar(max)	添付ファイル（JSON）

これらの拡張カラムの定義（ラベル名や選択肢など）は Sites テーブルの SiteSettings カラム（JSON）に格納されています。

Issues テーブル（期限付きテーブル）

Results テーブルとの違い

Issues テーブルは Results テーブルの全カラムに加えて、以下のカラムを持っています。

カラム	型	NULL	説明
`IssueId`	bigint	NO	レコード ID（主キー、IDENTITY）
`StartTime`	datetime	YES	開始日
`CompletionTime`	datetime	NO	完了日（NULL 非許容）
`WorkValue`	decimal	YES	作業量
`ProgressRate`	decimal	YES	進捗率
`RemainingWorkValue`	decimal	YES	残作業量

CompletionTime は NULL 非許容です。Issues テーブルにデータを INSERT する際は必ず値を指定してください。

API 内部実装の解析

Create の内部フロー

ResultModel.Create() と IssueModel.Create() を解析すると、以下の順序で処理されます。

SetByBeforeCreateServerScript() で作成前サーバスクリプトを実行
重複チェック（IfDuplicatedStatements()）
Rds.InsertItems() で Items テーブルに INSERT（ReferenceType を設定）
Rds.InsertResults() または Rds.InsertIssues() でメインテーブルに INSERT
InsertLinks() でリンク情報を作成
添付ファイルの処理
権限の設定（PermissionForCreating がある場合）
SCOPE_IDENTITY() で ID を取得
Rds.UpdateItems() で Items テーブルの Title・FullText を更新
SetByAfterCreateServerScript() で作成後サーバスクリプトを実行

Update の内部フロー

SetByBeforeUpdateServerScript() で更新前サーバスクリプトを実行
Versions.VerUp() でバージョンアップが必要か判定
バージョンアップする場合、Rds.ResultsCopyToStatement() で現在のレコードを Results_history にコピー
Ver をインクリメント
Rds.UpdateResults() でメインテーブルを UPDATE
楽観的排他制御による競合チェック
UpdateRelatedRecords() で Items テーブルの Title・FullText を同期
SetByAfterUpdateServerScript() で更新後サーバスクリプトを実行

ストアドプロシージャ

Results テーブル用

レコードの作成

CREATE PROCEDURE [dbo].[sp_CreateResult]
    @SiteId BIGINT,
    @UserId INT,
    @Title NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL,
    @Status INT = NULL,
    @ManagerUserId INT = NULL,
    @OwnerUserId INT = NULL,
    @NewResultId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Itemsテーブルに登録（API内部と同じ順序：Items → Results）
        INSERT INTO [Items] (
            [ReferenceType], [SiteId], [Title],
            [SearchIndexCreatedTime], [UpdatedTime]
        )
        VALUES (
            N'Results', @SiteId, ISNULL(@Title, N''),
            GETDATE(), GETDATE()
        );

        DECLARE @ReferenceId BIGINT = SCOPE_IDENTITY();

        -- Resultsテーブルに登録
        SET IDENTITY_INSERT [Results] ON;

        INSERT INTO [Results] (
            [SiteId], [ResultId], [Ver], [Title], [Body],
            [Status], [Manager], [Owner], [Locked], [Comments],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @SiteId, @ReferenceId, 1, @Title, @Body,
            @Status, @ManagerUserId, @OwnerUserId, 0, N'[]',
            @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET IDENTITY_INSERT [Results] OFF;
        SET @NewResultId = @ReferenceId;

        -- Itemsテーブルの FullText を更新
        UPDATE [Items]
        SET
            [Title] = ISNULL(@Title, N''),
            [FullText] = ISNULL(@Title, N'') + N' ' + ISNULL(@Body, N''),
            [SearchIndexCreatedTime] = GETDATE()
        WHERE
            [ReferenceId] = @NewResultId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            SET IDENTITY_INSERT [Results] OFF;
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

レコードの更新（履歴保存 + Items 同期）

CREATE PROCEDURE [dbo].[sp_UpdateResult]
    @ResultId BIGINT,
    @SiteId BIGINT,
    @UserId INT,
    @Title NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL,
    @Status INT = NULL,
    @ManagerUserId INT = NULL,
    @OwnerUserId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Results_history]
        SELECT * FROM [Results]
        WHERE [ResultId] = @ResultId;

        -- Resultsテーブルの更新
        UPDATE [Results]
        SET
            [Title] = ISNULL(@Title, [Title]),
            [Body] = ISNULL(@Body, [Body]),
            [Status] = ISNULL(@Status, [Status]),
            [Manager] = ISNULL(@ManagerUserId, [Manager]),
            [Owner] = ISNULL(@OwnerUserId, [Owner]),
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [SiteId] = @SiteId
            AND [ResultId] = @ResultId;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR(N'対象のレコードが見つかりません。', 16, 1);
        END

        -- Itemsテーブルの同期（タイトルとFullText）
        DECLARE @CurrentTitle NVARCHAR(1024);
        DECLARE @CurrentBody NVARCHAR(MAX);
        SELECT @CurrentTitle = [Title], @CurrentBody = [Body]
        FROM [Results] WHERE [ResultId] = @ResultId;

        UPDATE [Items]
        SET
            [Title] = ISNULL(@CurrentTitle, N''),
            [FullText] = ISNULL(@CurrentTitle, N'') + N' ' + ISNULL(@CurrentBody, N''),
            [SearchIndexCreatedTime] = GETDATE(),
            [UpdatedTime] = GETDATE()
        WHERE
            [ReferenceId] = @ResultId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

レコードの削除（ゴミ箱に移動）

CREATE PROCEDURE [dbo].[sp_DeleteResult]
    @ResultId BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Results_deletedテーブルへコピー
        INSERT INTO [Results_deleted]
        SELECT * FROM [Results]
        WHERE [ResultId] = @ResultId;

        -- Resultsテーブルから削除
        DELETE FROM [Results]
        WHERE [ResultId] = @ResultId;

        -- Itemsテーブルから削除
        DELETE FROM [Items]
        WHERE [ReferenceId] = @ResultId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

プリザンターの「ゴミ箱」機能は、Results_deleted（または Issues_deleted）テーブルのデータを復元する仕組みです。この方式に合わせておくことで、画面からの復元操作も可能になります。

レコードの物理削除

CREATE PROCEDURE [dbo].[sp_PhysicalDeleteResult]
    @ResultId BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 履歴の削除
        DELETE FROM [Results_history]
        WHERE [ResultId] = @ResultId;

        -- ゴミ箱の削除
        DELETE FROM [Results_deleted]
        WHERE [ResultId] = @ResultId;

        -- 添付ファイルの削除
        DELETE FROM [Binaries]
        WHERE [ReferenceId] = @ResultId;

        DELETE FROM [Binaries_deleted]
        WHERE [ReferenceId] = @ResultId;

        -- Itemsテーブルから削除
        DELETE FROM [Items]
        WHERE [ReferenceId] = @ResultId;

        -- Resultsテーブルから削除
        DELETE FROM [Results]
        WHERE [ResultId] = @ResultId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Issues テーブル用

レコードの作成

CREATE PROCEDURE [dbo].[sp_CreateIssue]
    @SiteId BIGINT,
    @UserId INT,
    @Title NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL,
    @StartTime DATETIME = NULL,
    @CompletionTime DATETIME,
    @WorkValue DECIMAL(18, 2) = NULL,
    @Status INT = 100,
    @ManagerUserId INT = NULL,
    @OwnerUserId INT = NULL,
    @NewIssueId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Itemsテーブルに登録
        INSERT INTO [Items] (
            [ReferenceType], [SiteId], [Title],
            [SearchIndexCreatedTime], [UpdatedTime]
        )
        VALUES (
            N'Issues', @SiteId, ISNULL(@Title, N''),
            GETDATE(), GETDATE()
        );

        DECLARE @ReferenceId BIGINT = SCOPE_IDENTITY();

        -- Issuesテーブルに登録
        SET IDENTITY_INSERT [Issues] ON;

        INSERT INTO [Issues] (
            [SiteId], [IssueId], [Ver], [Title], [Body],
            [StartTime], [CompletionTime], [WorkValue],
            [ProgressRate], [RemainingWorkValue],
            [Status], [Manager], [Owner], [Locked], [Comments],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @SiteId, @ReferenceId, 1, @Title, @Body,
            @StartTime, @CompletionTime, @WorkValue,
            0, @WorkValue,
            @Status, @ManagerUserId, @OwnerUserId, 0, N'[]',
            @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET IDENTITY_INSERT [Issues] OFF;
        SET @NewIssueId = @ReferenceId;

        -- Itemsテーブルの FullText を更新
        UPDATE [Items]
        SET
            [Title] = ISNULL(@Title, N''),
            [FullText] = ISNULL(@Title, N'') + N' ' + ISNULL(@Body, N''),
            [SearchIndexCreatedTime] = GETDATE()
        WHERE
            [ReferenceId] = @NewIssueId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            SET IDENTITY_INSERT [Issues] OFF;
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

レコードの更新（履歴保存 + Items 同期）

CREATE PROCEDURE [dbo].[sp_UpdateIssue]
    @IssueId BIGINT,
    @SiteId BIGINT,
    @UserId INT,
    @Title NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL,
    @Status INT = NULL,
    @ProgressRate DECIMAL(18, 2) = NULL,
    @StartTime DATETIME = NULL,
    @CompletionTime DATETIME = NULL,
    @ManagerUserId INT = NULL,
    @OwnerUserId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Issues_history]
        SELECT * FROM [Issues]
        WHERE [IssueId] = @IssueId;

        -- Issuesテーブルの更新
        UPDATE [Issues]
        SET
            [Title] = ISNULL(@Title, [Title]),
            [Body] = ISNULL(@Body, [Body]),
            [Status] = ISNULL(@Status, [Status]),
            [ProgressRate] = ISNULL(@ProgressRate, [ProgressRate]),
            [StartTime] = ISNULL(@StartTime, [StartTime]),
            [CompletionTime] = ISNULL(@CompletionTime, [CompletionTime]),
            [Manager] = ISNULL(@ManagerUserId, [Manager]),
            [Owner] = ISNULL(@OwnerUserId, [Owner]),
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [SiteId] = @SiteId
            AND [IssueId] = @IssueId;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR(N'対象のレコードが見つかりません。', 16, 1);
        END

        -- Itemsテーブルの同期
        DECLARE @CurrentTitle NVARCHAR(1024);
        DECLARE @CurrentBody NVARCHAR(MAX);
        SELECT @CurrentTitle = [Title], @CurrentBody = [Body]
        FROM [Issues] WHERE [IssueId] = @IssueId;

        UPDATE [Items]
        SET
            [Title] = ISNULL(@CurrentTitle, N''),
            [FullText] = ISNULL(@CurrentTitle, N'') + N' ' + ISNULL(@CurrentBody, N''),
            [SearchIndexCreatedTime] = GETDATE(),
            [UpdatedTime] = GETDATE()
        WHERE
            [ReferenceId] = @IssueId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

バッチ（大量データ）処理用ストアドプロシージャ

データ移行やバッチ処理では、大量のレコードを効率的に一括操作する必要があります。ここでは、テーブル値パラメータ（TVP）を使った一括 CRUD 操作のストアドプロシージャを紹介します。

テーブル型の定義

一括操作で使用するテーブル型（ユーザー定義テーブル型）を先に作成します。

Results 用テーブル型

CREATE TYPE [dbo].[ResultBatchType] AS TABLE (
    [RowNo]          INT,
    [Title]          NVARCHAR(1024) NULL,
    [Body]           NVARCHAR(MAX) NULL,
    [Status]         INT NULL,
    [Manager]        INT NULL,
    [Owner]          INT NULL,
    [ClassA]         NVARCHAR(1024) NULL,
    [ClassB]         NVARCHAR(1024) NULL,
    [ClassC]         NVARCHAR(1024) NULL,
    [NumA]           DECIMAL(18, 2) NULL,
    [NumB]           DECIMAL(18, 2) NULL,
    [NumC]           DECIMAL(18, 2) NULL,
    [DateA]          DATETIME NULL,
    [DateB]          DATETIME NULL,
    [DateC]          DATETIME NULL,
    [DescriptionA]   NVARCHAR(MAX) NULL,
    [DescriptionB]   NVARCHAR(MAX) NULL,
    [DescriptionC]   NVARCHAR(MAX) NULL,
    [CheckA]         BIT NULL,
    [CheckB]         BIT NULL,
    [CheckC]         BIT NULL
);

Issues 用テーブル型

CREATE TYPE [dbo].[IssueBatchType] AS TABLE (
    [RowNo]          INT,
    [Title]          NVARCHAR(1024) NULL,
    [Body]           NVARCHAR(MAX) NULL,
    [Status]         INT NULL,
    [Manager]        INT NULL,
    [Owner]          INT NULL,
    [StartTime]      DATETIME NULL,
    [CompletionTime] DATETIME NULL,
    [WorkValue]      DECIMAL(18, 2) NULL,
    [ProgressRate]   DECIMAL(18, 2) NULL,
    [ClassA]         NVARCHAR(1024) NULL,
    [ClassB]         NVARCHAR(1024) NULL,
    [ClassC]         NVARCHAR(1024) NULL,
    [NumA]           DECIMAL(18, 2) NULL,
    [NumB]           DECIMAL(18, 2) NULL,
    [NumC]           DECIMAL(18, 2) NULL,
    [DateA]          DATETIME NULL,
    [DateB]          DATETIME NULL,
    [DateC]          DATETIME NULL,
    [DescriptionA]   NVARCHAR(MAX) NULL,
    [DescriptionB]   NVARCHAR(MAX) NULL,
    [DescriptionC]   NVARCHAR(MAX) NULL,
    [CheckA]         BIT NULL,
    [CheckB]         BIT NULL,
    [CheckC]         BIT NULL
);

拡張カラム（ClassA 〜 ClassZ 等）は使用する分だけテーブル型に含めてください。上記の例では A 〜 C の 3 カラムずつを定義していますが、実際のサイト設定に合わせて追加・削除してください。

Results テーブル用バッチ操作

一括作成（バッチ INSERT）

CREATE PROCEDURE [dbo].[sp_BulkCreateResults]
    @SiteId BIGINT,
    @UserId INT,
    @Data [dbo].[ResultBatchType] READONLY,
    @InsertedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @InsertedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @Now DATETIME = GETDATE();

        -- 一時テーブルでItems登録→ReferenceId取得を一括処理
        DECLARE @InsertedItems TABLE (
            [ReferenceId] BIGINT,
            [RowNo] INT
        );

        -- 1. Itemsテーブルに一括登録
        MERGE INTO [Items] AS target
        USING (
            SELECT
                [RowNo],
                N'Results' AS [ReferenceType],
                @SiteId AS [SiteId],
                ISNULL([Title], N'') AS [Title]
            FROM @Data
        ) AS source
        ON 1 = 0  -- 常にINSERT
        WHEN NOT MATCHED THEN
            INSERT ([ReferenceType], [SiteId], [Title],
                    [SearchIndexCreatedTime], [UpdatedTime])
            VALUES (source.[ReferenceType], source.[SiteId], source.[Title],
                    @Now, @Now)
        OUTPUT inserted.[ReferenceId], source.[RowNo]
        INTO @InsertedItems;

        -- 2. Resultsテーブルに一括登録
        SET IDENTITY_INSERT [Results] ON;

        INSERT INTO [Results] (
            [SiteId], [ResultId], [Ver],
            [Title], [Body], [Status], [Manager], [Owner],
            [ClassA], [ClassB], [ClassC],
            [NumA], [NumB], [NumC],
            [DateA], [DateB], [DateC],
            [DescriptionA], [DescriptionB], [DescriptionC],
            [CheckA], [CheckB], [CheckC],
            [Locked], [Comments],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        SELECT
            @SiteId, ii.[ReferenceId], 1,
            d.[Title], d.[Body], d.[Status], d.[Manager], d.[Owner],
            d.[ClassA], d.[ClassB], d.[ClassC],
            d.[NumA], d.[NumB], d.[NumC],
            d.[DateA], d.[DateB], d.[DateC],
            d.[DescriptionA], d.[DescriptionB], d.[DescriptionC],
            d.[CheckA], d.[CheckB], d.[CheckC],
            0, N'[]',
            @UserId, @UserId, @Now, @Now
        FROM @Data d
        INNER JOIN @InsertedItems ii ON d.[RowNo] = ii.[RowNo];

        SET IDENTITY_INSERT [Results] OFF;

        -- 3. ItemsテーブルのFullTextを一括更新
        UPDATE i
        SET
            [FullText] = ISNULL(r.[Title], N'') + N' ' + ISNULL(r.[Body], N''),
            [SearchIndexCreatedTime] = @Now
        FROM [Items] i
        INNER JOIN [Results] r ON i.[ReferenceId] = r.[ResultId]
        INNER JOIN @InsertedItems ii ON r.[ResultId] = ii.[ReferenceId];

        SET @InsertedCount = (SELECT COUNT(*) FROM @InsertedItems);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            SET IDENTITY_INSERT [Results] OFF;
            ROLLBACK TRANSACTION;
        END
        THROW;
    END CATCH
END;

一括更新（バッチ UPDATE）

プリザンターの BulkUpdate API と同様に、指定条件に一致するレコードを一括で更新します。更新前のレコードは _history テーブルに保存します。

CREATE PROCEDURE [dbo].[sp_BulkUpdateResults]
    @SiteId BIGINT,
    @UserId INT,
    @Status INT = NULL,
    @Manager INT = NULL,
    @Owner INT = NULL,
    @ClassA NVARCHAR(1024) = NULL,
    @WhereStatus INT = NULL,
    @WhereOwner INT = NULL,
    @UpdatedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @UpdatedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @Now DATETIME = GETDATE();

        -- 対象レコードのIDを収集
        DECLARE @TargetIds TABLE ([ResultId] BIGINT);

        INSERT INTO @TargetIds
        SELECT [ResultId] FROM [Results]
        WHERE [SiteId] = @SiteId
            AND (@WhereStatus IS NULL OR [Status] = @WhereStatus)
            AND (@WhereOwner IS NULL OR [Owner] = @WhereOwner);

        -- 1. 更新前のレコードを履歴テーブルに一括保存
        INSERT INTO [Results_history]
        SELECT r.* FROM [Results] r
        INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];

        -- 2. Resultsテーブルを一括更新
        UPDATE r
        SET
            [Status] = ISNULL(@Status, r.[Status]),
            [Manager] = ISNULL(@Manager, r.[Manager]),
            [Owner] = ISNULL(@Owner, r.[Owner]),
            [ClassA] = ISNULL(@ClassA, r.[ClassA]),
            [Ver] = r.[Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = @Now
        FROM [Results] r
        INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];

        SET @UpdatedCount = @@ROWCOUNT;

        -- 3. Itemsテーブルの更新日時を同期
        UPDATE i
        SET [UpdatedTime] = @Now
        FROM [Items] i
        INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[ResultId];

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

一括削除（バッチ DELETE）

指定サイトの条件に一致するレコードを一括でゴミ箱に移動します。

CREATE PROCEDURE [dbo].[sp_BulkDeleteResults]
    @SiteId BIGINT,
    @WhereStatus INT = NULL,
    @DeletedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @DeletedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 対象レコードのIDを収集
        DECLARE @TargetIds TABLE ([ResultId] BIGINT);

        INSERT INTO @TargetIds
        SELECT [ResultId] FROM [Results]
        WHERE [SiteId] = @SiteId
            AND (@WhereStatus IS NULL OR [Status] = @WhereStatus);

        -- 1. Results_deletedテーブルへ一括コピー
        INSERT INTO [Results_deleted]
        SELECT r.* FROM [Results] r
        INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];

        SET @DeletedCount = @@ROWCOUNT;

        -- 2. Resultsテーブルから一括削除
        DELETE r FROM [Results] r
        INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];

        -- 3. Itemsテーブルから一括削除
        DELETE i FROM [Items] i
        INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[ResultId];

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

一括物理削除（バッチ PHYSICAL DELETE）

サイト内のレコードを履歴・ゴミ箱・添付ファイルも含めて完全に一括削除します。

CREATE PROCEDURE [dbo].[sp_BulkPhysicalDeleteResults]
    @SiteId BIGINT,
    @DeletedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @DeletedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 対象レコードのIDを収集
        DECLARE @TargetIds TABLE ([ResultId] BIGINT);

        INSERT INTO @TargetIds
        SELECT [ResultId] FROM [Results]
        WHERE [SiteId] = @SiteId;

        SET @DeletedCount = (SELECT COUNT(*) FROM @TargetIds);

        -- 1. 履歴テーブルの一括削除
        DELETE rh FROM [Results_history] rh
        INNER JOIN @TargetIds t ON rh.[ResultId] = t.[ResultId];

        -- 2. ゴミ箱テーブルの一括削除
        DELETE rd FROM [Results_deleted] rd
        INNER JOIN @TargetIds t ON rd.[ResultId] = t.[ResultId];

        -- 3. 添付ファイルの一括削除
        DELETE b FROM [Binaries] b
        INNER JOIN @TargetIds t ON b.[ReferenceId] = t.[ResultId];

        DELETE bd FROM [Binaries_deleted] bd
        INNER JOIN @TargetIds t ON bd.[ReferenceId] = t.[ResultId];

        -- 4. Itemsテーブルの一括削除
        DELETE i FROM [Items] i
        INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[ResultId];

        -- 5. Resultsテーブルの一括削除
        DELETE r FROM [Results] r
        INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Issues テーブル用バッチ操作

一括作成（バッチ INSERT）

CREATE PROCEDURE [dbo].[sp_BulkCreateIssues]
    @SiteId BIGINT,
    @UserId INT,
    @Data [dbo].[IssueBatchType] READONLY,
    @InsertedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @InsertedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @Now DATETIME = GETDATE();

        DECLARE @InsertedItems TABLE (
            [ReferenceId] BIGINT,
            [RowNo] INT
        );

        -- 1. Itemsテーブルに一括登録
        MERGE INTO [Items] AS target
        USING (
            SELECT
                [RowNo],
                N'Issues' AS [ReferenceType],
                @SiteId AS [SiteId],
                ISNULL([Title], N'') AS [Title]
            FROM @Data
        ) AS source
        ON 1 = 0
        WHEN NOT MATCHED THEN
            INSERT ([ReferenceType], [SiteId], [Title],
                    [SearchIndexCreatedTime], [UpdatedTime])
            VALUES (source.[ReferenceType], source.[SiteId], source.[Title],
                    @Now, @Now)
        OUTPUT inserted.[ReferenceId], source.[RowNo]
        INTO @InsertedItems;

        -- 2. Issuesテーブルに一括登録
        SET IDENTITY_INSERT [Issues] ON;

        INSERT INTO [Issues] (
            [SiteId], [IssueId], [Ver],
            [Title], [Body], [Status], [Manager], [Owner],
            [StartTime], [CompletionTime], [WorkValue],
            [ProgressRate], [RemainingWorkValue],
            [ClassA], [ClassB], [ClassC],
            [NumA], [NumB], [NumC],
            [DateA], [DateB], [DateC],
            [DescriptionA], [DescriptionB], [DescriptionC],
            [CheckA], [CheckB], [CheckC],
            [Locked], [Comments],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        SELECT
            @SiteId, ii.[ReferenceId], 1,
            d.[Title], d.[Body], d.[Status], d.[Manager], d.[Owner],
            d.[StartTime],
            ISNULL(d.[CompletionTime], DATEADD(DAY, 7, @Now)),
            d.[WorkValue],
            ISNULL(d.[ProgressRate], 0),
            d.[WorkValue],
            d.[ClassA], d.[ClassB], d.[ClassC],
            d.[NumA], d.[NumB], d.[NumC],
            d.[DateA], d.[DateB], d.[DateC],
            d.[DescriptionA], d.[DescriptionB], d.[DescriptionC],
            d.[CheckA], d.[CheckB], d.[CheckC],
            0, N'[]',
            @UserId, @UserId, @Now, @Now
        FROM @Data d
        INNER JOIN @InsertedItems ii ON d.[RowNo] = ii.[RowNo];

        SET IDENTITY_INSERT [Issues] OFF;

        -- 3. ItemsテーブルのFullTextを一括更新
        UPDATE i
        SET
            [FullText] = ISNULL(iss.[Title], N'') + N' ' + ISNULL(iss.[Body], N''),
            [SearchIndexCreatedTime] = @Now
        FROM [Items] i
        INNER JOIN [Issues] iss ON i.[ReferenceId] = iss.[IssueId]
        INNER JOIN @InsertedItems ii ON iss.[IssueId] = ii.[ReferenceId];

        SET @InsertedCount = (SELECT COUNT(*) FROM @InsertedItems);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            SET IDENTITY_INSERT [Issues] OFF;
            ROLLBACK TRANSACTION;
        END
        THROW;
    END CATCH
END;

一括更新（バッチ UPDATE）

CREATE PROCEDURE [dbo].[sp_BulkUpdateIssues]
    @SiteId BIGINT,
    @UserId INT,
    @Status INT = NULL,
    @Manager INT = NULL,
    @Owner INT = NULL,
    @CompletionTime DATETIME = NULL,
    @ProgressRate DECIMAL(18, 2) = NULL,
    @ClassA NVARCHAR(1024) = NULL,
    @WhereStatus INT = NULL,
    @WhereOwner INT = NULL,
    @UpdatedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @UpdatedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @Now DATETIME = GETDATE();

        -- 対象レコードのIDを収集
        DECLARE @TargetIds TABLE ([IssueId] BIGINT);

        INSERT INTO @TargetIds
        SELECT [IssueId] FROM [Issues]
        WHERE [SiteId] = @SiteId
            AND (@WhereStatus IS NULL OR [Status] = @WhereStatus)
            AND (@WhereOwner IS NULL OR [Owner] = @WhereOwner);

        -- 1. 更新前のレコードを履歴テーブルに一括保存
        INSERT INTO [Issues_history]
        SELECT iss.* FROM [Issues] iss
        INNER JOIN @TargetIds t ON iss.[IssueId] = t.[IssueId];

        -- 2. Issuesテーブルを一括更新
        UPDATE iss
        SET
            [Status] = ISNULL(@Status, iss.[Status]),
            [Manager] = ISNULL(@Manager, iss.[Manager]),
            [Owner] = ISNULL(@Owner, iss.[Owner]),
            [CompletionTime] = ISNULL(@CompletionTime, iss.[CompletionTime]),
            [ProgressRate] = ISNULL(@ProgressRate, iss.[ProgressRate]),
            [ClassA] = ISNULL(@ClassA, iss.[ClassA]),
            [Ver] = iss.[Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = @Now
        FROM [Issues] iss
        INNER JOIN @TargetIds t ON iss.[IssueId] = t.[IssueId];

        SET @UpdatedCount = @@ROWCOUNT;

        -- 3. Itemsテーブルの更新日時を同期
        UPDATE i
        SET [UpdatedTime] = @Now
        FROM [Items] i
        INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[IssueId];

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

一括削除（バッチ DELETE）

CREATE PROCEDURE [dbo].[sp_BulkDeleteIssues]
    @SiteId BIGINT,
    @WhereStatus INT = NULL,
    @DeletedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @DeletedCount = 0;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @TargetIds TABLE ([IssueId] BIGINT);

        INSERT INTO @TargetIds
        SELECT [IssueId] FROM [Issues]
        WHERE [SiteId] = @SiteId
            AND (@WhereStatus IS NULL OR [Status] = @WhereStatus);

        -- 1. Issues_deletedテーブルへ一括コピー
        INSERT INTO [Issues_deleted]
        SELECT iss.* FROM [Issues] iss
        INNER JOIN @TargetIds t ON iss.[IssueId] = t.[IssueId];

        SET @DeletedCount = @@ROWCOUNT;

        -- 2. Issuesテーブルから一括削除
        DELETE iss FROM [Issues] iss
        INNER JOIN @TargetIds t ON iss.[IssueId] = t.[IssueId];

        -- 3. Itemsテーブルから一括削除
        DELETE i FROM [Items] i
        INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[IssueId];

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

バッチ操作の使用例

Results の一括作成

-- テーブル型変数にデータを準備
DECLARE @BatchData [dbo].[ResultBatchType];

INSERT INTO @BatchData ([RowNo], [Title], [Status], [Owner], [ClassA])
VALUES
    (1, N'案件A', 100, 2, N'カテゴリ1'),
    (2, N'案件B', 100, 3, N'カテゴリ2'),
    (3, N'案件C', 200, 2, N'カテゴリ1');

-- 一括作成の実行
DECLARE @Count INT;
EXEC [dbo].[sp_BulkCreateResults]
    @SiteId = 12345,
    @UserId = 1,
    @Data = @BatchData,
    @InsertedCount = @Count OUTPUT;

PRINT N'作成件数: ' + CAST(@Count AS NVARCHAR);

Issues の一括ステータス更新

-- ステータス100（未着手）のレコードを一括で200（処理中）に変更
DECLARE @Count INT;
EXEC [dbo].[sp_BulkUpdateIssues]
    @SiteId = 12345,
    @UserId = 1,
    @Status = 200,
    @WhereStatus = 100,
    @UpdatedCount = @Count OUTPUT;

PRINT N'更新件数: ' + CAST(@Count AS NVARCHAR);

Results の条件付き一括削除

-- ステータス900（完了）のレコードを一括でゴミ箱に移動
DECLARE @Count INT;
EXEC [dbo].[sp_BulkDeleteResults]
    @SiteId = 12345,
    @WhereStatus = 900,
    @DeletedCount = @Count OUTPUT;

PRINT N'削除件数: ' + CAST(@Count AS NVARCHAR);

CSV からの大量インポート（BULK INSERT との組み合わせ）

外部の CSV ファイルから BULK INSERT でステージングテーブルに取り込み、テーブル値パラメータ経由でバッチ作成を呼び出すパターンです。

-- ステージングテーブルの作成
CREATE TABLE #StagingResults (
    [RowNo] INT IDENTITY(1, 1),
    [Title] NVARCHAR(1024),
    [Body] NVARCHAR(MAX),
    [Status] INT,
    [Owner] INT,
    [ClassA] NVARCHAR(1024)
);

-- CSVファイルからステージングテーブルにインポート
BULK INSERT #StagingResults
FROM 'C:\Import\results.csv'
WITH (
    CODEPAGE = '65001',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

-- テーブル値パラメータに変換して一括作成
DECLARE @BatchData [dbo].[ResultBatchType];

INSERT INTO @BatchData ([RowNo], [Title], [Body], [Status], [Owner], [ClassA])
SELECT [RowNo], [Title], [Body], [Status], [Owner], [ClassA]
FROM #StagingResults;

DECLARE @Count INT;
EXEC [dbo].[sp_BulkCreateResults]
    @SiteId = 12345,
    @UserId = 1,
    @Data = @BatchData,
    @InsertedCount = @Count OUTPUT;

PRINT N'インポート件数: ' + CAST(@Count AS NVARCHAR);

DROP TABLE #StagingResults;

Read（読取）

Results レコードの一覧取得

SELECT
    r.[ResultId],
    i.[Title],
    r.[Status],
    r.[Owner],
    r.[Manager],
    r.[CreatedTime],
    r.[UpdatedTime]
FROM
    [Results] r
    INNER JOIN [Items] i
        ON r.[ResultId] = i.[ReferenceId]
WHERE
    r.[SiteId] = @SiteId
ORDER BY
    r.[ResultId] DESC;

Issues レコードの一覧取得

SELECT
    iss.[IssueId],
    i.[Title],
    iss.[Status],
    iss.[StartTime],
    iss.[CompletionTime],
    iss.[ProgressRate],
    iss.[Owner],
    iss.[Manager]
FROM
    [Issues] iss
    INNER JOIN [Items] i
        ON iss.[IssueId] = i.[ReferenceId]
WHERE
    iss.[SiteId] = @SiteId
ORDER BY
    iss.[CompletionTime];

レコードの変更履歴を取得

SELECT
    rh.[Ver],
    rh.[Title],
    rh.[Status],
    rh.[Updator],
    u.[Name] AS [UpdaterName],
    rh.[UpdatedTime]
FROM
    [Results_history] rh
    LEFT JOIN [Users] u
        ON rh.[Updator] = u.[UserId]
WHERE
    rh.[ResultId] = @ResultId
ORDER BY
    rh.[Ver] DESC;

まとめ

第 4 回では、Results テーブルと Issues テーブルの CRUD 操作を紹介しました。

API の内部実装では、Items テーブル → メインテーブルの順で INSERT が実行される
更新時は _history テーブルに更新前のレコードを保存してからメインテーブルを更新する
Items テーブルの Title・FullText は作成時・更新時の両方で同期が必要
Issues テーブルは CompletionTime（NULL 非許容）などの固有カラムが追加されている
削除操作は _deleted テーブルへの移動方式を使うとプリザンターのゴミ箱機能と互換性を保てる
バッチ処理では、テーブル値パラメータ（TVP）と MERGE 文を組み合わせて Items → メインテーブルの一括登録を効率的に実行できる
一括更新・一括削除では、対象 ID を一時テーブルに収集してから JOIN ベースで処理することで、大量データでもパフォーマンスを維持できる

次回は Wikis テーブルの CRUD 操作を紹介します。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up