GetDocument

GetDocument.sql

/****** Object:  UserDefinedFunction [dbo].[GetDocument]    Script Date: 6/7/2022 2:52:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetDocument](@documentId VARCHAR(36), @revision INT = NULL)
    RETURNS @table TABLE(
                            Id           VARCHAR(36),
                            CreatedDate  DATETIMEOFFSET,
                            ModifiedDate DATETIMEOFFSET,
                            DeletedDate  DATETIMEOFFSET,
                            State        VARCHAR(16),
                            DocumentId   VARCHAR(36),
                            [Key]        NVARCHAR(300),
                            Value        NVARCHAR(max),
                            Type         VARCHAR(20),
                            Revision     INT,
                            INDEX GetDocumentIndexDocumentId NONCLUSTERED (DocumentId),
                            INDEX GetDocumentIndexRevision NONCLUSTERED (Revision),
                            INDEX GetDocumentIndexKey NONCLUSTERED ([Key])
                        )
    AS
    BEGIN
        DECLARE @currentRevision AS INT
        DECLARE @tempTable TABLE(
                                    Id           VARCHAR(36) primary key,
                                    CreatedDate  DATETIMEOFFSET,
                                    ModifiedDate DATETIMEOFFSET,
                                    DeletedDate  DATETIMEOFFSET,
                                    State        VARCHAR(16),
                                    DocumentId   VARCHAR(36),
                                    [Key]        NVARCHAR(300),
                                    Value        NVARCHAR(max),
                                    Type         VARCHAR(20),
                                    Revision     INT
                                )
        DECLARE @id           VARCHAR(36)
        DECLARE @createdDate  DATETIMEOFFSET
        DECLARE @modifiedDate DATETIMEOFFSET
        DECLARE @deletedDate  DATETIMEOFFSET
        DECLARE @state        VARCHAR(16)
        DECLARE @key          NVARCHAR(300)
        DECLARE @value        NVARCHAR(max)
        DECLARE @type         VARCHAR(20)
        DECLARE @action       VARCHAR(16)

        SELECT @currentRevision = MAX(Revision) FROM dbo.Document WHERE Id = @documentId

        IF @currentRevision = 0 OR @currentRevision IS NULL
            BEGIN
                INSERT INTO @table(Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision)
                SELECT Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision FROM dbo.DocumentDetail with(nolock)
                WHERE  DocumentId = @documentId AND Revision IS NULL
                RETURN
            END

        INSERT INTO @tempTable(Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision)
        SELECT Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision FROM dbo.DocumentDetail with(nolock)
        WHERE  DocumentId = @documentId AND Revision IS NULL

        UPDATE @tempTable SET Revision = @currentRevision, CreatedDate = ModifiedDate WHERE 1 = 1

        IF @revision > @currentRevision OR @revision < 0 SET @revision = @currentRevision

        SET @currentRevision = @currentRevision - 1
        WHILE (@revision IS NOT NULL AND @currentRevision >= @revision) OR (@revision IS NULL AND @currentRevision >= 0)
            BEGIN

                IF @revision IS NULL
                    INSERT INTO @table(Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision)
                    SELECT Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision FROM @tempTable

                DECLARE cursorRevision CURSOR FOR
                    SELECT Id, CreatedDate, ModifiedDate, DeletedDate, State, [Key], Value, Type, Action FROM dbo.DocumentDetail with(nolock)
                    WHERE DocumentId = @documentId AND Revision = @currentRevision

                OPEN cursorRevision

                FETCH NEXT FROM cursorRevision INTO @id, @createdDate, @modifiedDate, @deletedDate, @state, @key, @value, @type, @action

                WHILE @@FETCH_STATUS = 0
                    BEGIN

                        IF @action = 'ADDED'
                            DELETE FROM @tempTable WHERE [Key] = @key
                        ELSE IF @action = 'MODIFIED'
                            UPDATE @tempTable
                            SET [Value] = @value,
                                [Type] = @type,
                                Revision = @currentRevision,
                                CreatedDate = @createdDate,
                                ModifiedDate = ISNULL(@modifiedDate, @createdDate),
                                DeletedDate = @deletedDate,
                                State = @state
                            WHERE [Key] = @key
                        ELSE IF @action = 'DELETED'
                            INSERT @tempTable(Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision)
                            VALUES (@id, @createdDate, @modifiedDate, @deletedDate, @state, @documentId, @key, @value, @type, @currentRevision)

                        FETCH NEXT FROM cursorRevision INTO @id, @createdDate, @modifiedDate, @deletedDate, @state, @key, @value, @type, @action
                    END

                UPDATE @tempTable SET Revision = @currentRevision WHERE 1 = 1

                CLOSE cursorRevision
                DEALLOCATE cursorRevision

                SET @currentRevision = @currentRevision - 1
            END

        INSERT INTO @table(Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision)
        SELECT Id, CreatedDate, ModifiedDate, DeletedDate, State, DocumentId, [Key], Value, Type, Revision FROM @tempTable

        RETURN
    END
GO