Online Spreadsheet Database

SQL
Author

Gary Newport

Published

Sunday, December 1, 2019

Description

A while a go I was working on a spreadsheet style applicaiton for the web, and we needed a method to store the 2 dimentional data in a 1 deminentional database. This was the resultant database structure, which is a linked list, which allowed both data and formula’s to included. There was either a template structure so you could load predefined sheets. I’ll get round to recreating the web page…. eventually.

GreenDog
  • Tables
    • R2Data
    • R2Item
    • R3Data
    • R6TemplateItem

ItemId, ParentId, GdDdId, Format, DivisionId, Depth, ProcessId, Attribute

CREATE TABLE R6TemplateItem (
       R6TemplateItemId INT NOT NULL IDENTITY PRIMARY KEY
     , R6ParentId INT
     , R6GdDdId VARCHAR(50)
     , R6Depth INT
     , R6Lineage VARCHAR(100)
     , R6Format VARCHAR(50)
     , R6Row INT
     , R6Col INT
     , R6Value VARCHAR(250)
     , R6SeqNo INT
     , R6ItemLinkId INT
     , CONSTRAINT FK_R6TemplateItem_1 FOREIGN KEY (R6ParentId)
                  REFERENCES R6TemplateItem (R6TemplateItemId)
);

CREATE TABLE R5Division (
       R5DivisionId INT NOT NULL IDENTITY PRIMARY KEY
     , R5ProcessId INT
     , R5CountryId INT
     , R5ItemId INT
     , R5Name VARCHAR(50)
     , R5RecipientId INT
     , R5Seq INT
     , R5Creator BIT
     , R5Valid BIT
     , R5Deleted BIT
     , CONSTRAINT FK_R5Division_1 FOREIGN KEY (R5ProcessId)
                  REFERENCES Q4Process (Q4ProcessId)
     , CONSTRAINT FK_R5Division_3 FOREIGN KEY (R5CountryId)
                  REFERENCES P3Country (P3CountryId)
     , CONSTRAINT FK_R5Division_2 FOREIGN KEY (R5ItemId)
                  REFERENCES R2Item (R2ItemId) ON DELETE CASCADE
);

CREATE TABLE R3Data (
       R3DataId INT NOT NULL IDENTITY PRIMARY KEY
     , R3ItemId INT NOT NULL
     , R3DivisionId INT
     , R3Value VARCHAR(250)
     , R3ItemLinkId INT
     , R3TaxPackageRef VARCHAR(50)
     , R3ProcessId INT
     , R3Attribute INT
     , CONSTRAINT FK_R4Data_4 FOREIGN KEY (R3ItemId)
                  REFERENCES R2Item (R2ItemId) ON DELETE CASCADE
     , CONSTRAINT FK_R3Data_3 FOREIGN KEY (R3DivisionId)
                  REFERENCES R5Division (R5DivisionId)
);

CREATE TABLE R1IpackTemplate (
       R1IpackTemplateId INT NOT NULL IDENTITY PRIMARY KEY
     , R1Name VARCHAR(20)
     , R1TemplateItemId INT
     , R1Deleted BIT
     , CONSTRAINT FK_R1IpackTemplate_2 FOREIGN KEY (R1TemplateItemId)
                  REFERENCES R6TemplateItem (R6TemplateItemId) ON DELETE CASCADE
);

Get Item Tree

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'gd_getItemTree'
       AND    type = 'P')
    DROP PROCEDURE gd_getItemTree
GO

CREATE PROCEDURE gd_getItemTree
    @ItemId int = NULL,
    @DivisionId int = NULL
AS

    IF @ItemId IS NOT NULL and @DivisionId = -1
    BEGIN
        SELECT a.R2ItemId AS ItemId,
            a.R2ParentId AS ParentId,
            a.R2GdDdId AS GdDdId,
            a.R2Format AS Format,
            b.R3Value AS Value,
            b.R3DivisionId AS DivisionId,
            a.R2Depth AS Depth,
            b.R3ProcessId AS ProcessId,
            b.R3Attribute AS Attribute
        FROM R2Item a, R3Data b
        WHERE a.R2Lineage LIKE '%'+cast( @ItemId AS varchar(10)) +'%'
            and a.R2GdDdId IN ( SELECT R6GdDdId FROM R6TemplateItem WHERE R6GdDdId = a.R2GdDdId and R6ParentId = 0)
            and b.R3ItemId = a.R2ItemId
            and b.R3DivisionId IS NULL
        ORDER BY a.R2SeqNo
    END
    ELSE IF @ItemId IS NOT NULL and @DivisionId IS NULL
    BEGIN
        SELECT a.R2ItemId AS ItemId,
            a.R2ParentId AS ParentId,
            a.R2GdDdId AS GdDdId,
            a.R2Format AS Format,
            b.R3Value AS Value,
            b.R3DivisionId AS DivisionId,
            a.R2Depth AS Depth,
            b.R3ProcessId AS ProcessId,
            b.R3Attribute AS Attribute
        FROM R2Item a, R3Data b
        WHERE a.R2Lineage LIKE '%'+cast( @ItemId AS varchar(10)) +'%'
            and a.R2GdDdId IN ( SELECT R6GdDdId FROM R6TemplateItem WHERE R6GdDdId = a.R2GdDdId and R6ParentId = 0)
            and b.R3ItemId = a.R2ItemId
            and (b.R3DivisionId IS NULL
            or b.R3DivisionId = (
                SELECT R5DivisionId
                FROM R5Division
                WHERE b.R3DivisionId IS NOT NULL
                and R5DivisionId = b.R3DivisionId
                and R5Deleted IS NULL))
        ORDER BY a.R2SeqNo
    END
    ELSE IF @ItemId IS NOT NULL and @DivisionId IS NOT NULL
    BEGIN
        -- Make sure its not marked as deleted.
        IF( SELECT R5DivisionId FROM R5Division WHERE R5DivisionId = @DivisionId and R5Deleted IS NULL) > 0
        BEGIN
            SELECT a.R2ItemId AS ItemId,
                a.R2ParentId AS ParentId,
                a.R2GdDdId AS GdDdId,
                a.R2Format AS Format,
                b.R3Value AS Value,
                b.R3DivisionId AS DivisionId,
                a.R2Depth AS Depth,
                b.R3ProcessId AS ProcessId,
                b.R3Attribute AS Attribute
            FROM R2Item a, R3Data b
            WHERE a.R2Lineage LIKE '%'+cast( @ItemId AS varchar(10)) +'%'
                and a.R2GdDdId IN ( SELECT R6GdDdId FROM R6TemplateItem WHERE R6GdDdId = a.R2GdDdId and R6ParentId = 0)
                and b.R3ItemId = a.R2ItemId
                and (b.R3DivisionId = @DivisionId or b.R3DivisionId IS NULL)
            ORDER BY a.R2SeqNo
        END
    END
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
-- EXECUTE gd_getItemTree 1803, 167
GO

Get Item

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'gd_getItem'
       AND    type = 'P')
    DROP PROCEDURE gd_getItem
GO

CREATE PROCEDURE gd_getItem
    @ItemId int = NULL,
    @DivisionId int = NULL
AS

    DECLARE @TemplateParentId int;
    SET @TemplateParentId = (SELECT a.R6TemplateItemId FROM R6TemplateItem a
                WHERE a.R6GdDdId = (SELECT a.R2GdDdId AS GdDdId FROM R2Item a WHERE a.R2ItemId = @ItemId))

    IF @ItemId IS NOT NULL and @DivisionId IS NULL
    BEGIN
        SELECT a.R2ItemId AS ItemId,
            a.R2ParentId AS ParentId,
            b.R3DataId AS DataId,
            a.R2GdDdId AS GdDdId,
            c.R6Format AS Format,
            c.R6Row AS Row,
            c.R6Col AS Col,
            a.R2SeqNo AS SeqNo,
            c.R6SeqNo AS TemplateSeqNo,
            b.R3DivisionId AS DivisionId,
            b.R3Value AS Value,
            b.R3ItemLinkId AS ItemLinkId,
            b.R3TaxPackageRef AS TaxPackageRef,
            b.R3ProcessId AS ProcessId,
            b.R3Attribute AS Attribute
        FROM R2Item a, R3Data b, R6TemplateItem c
        WHERE ( a.R2ItemId = @ItemId or a.R2ParentId = @ItemId
            or a.R2ParentId = ( SELECT R2ItemId FROM R2Item WHERE R2ParentId = 0
                and R2GdDdId = ( SELECT R2GdDdId FROM R2Item WHERE R2ItemId = @ItemId))
            or a.R2ItemId = ( SELECT R2ItemId FROM R2Item WHERE R2ParentId = 0
                and R2GdDdId = ( SELECT R2GdDdId FROM R2Item WHERE R2ItemId = @ItemId)))
            and b.R3ItemId = a.R2ItemId
            and a.R2GdDdId = c.R6GdDdId
            and (c.R6ParentId = @TemplateParentId
                or c.R6TemplateItemId = @TemplateParentId)
            order by c.R6Row, c.R6Col
    END
    IF @ItemId IS NOT NULL and @DivisionId IS NOT NULL
    BEGIN
        SELECT a.R2ItemId AS ItemId,
            a.R2ParentId AS ParentId,
            b.R3DataId AS DataId,
            a.R2GdDdId AS GdDdId,
            c.R6Format AS Format,
            c.R6Row AS Row,
            c.R6Col AS Col,
            a.R2SeqNo AS SeqNo,
            c.R6SeqNo AS TemplateSeqNo,
            b.R3DivisionId AS DivisionId,
            b.R3Value AS Value,
            b.R3ItemLinkId AS ItemLinkId,
            b.R3TaxPackageRef AS TaxPackageRef,
            b.R3ProcessId AS ProcessId,
            b.R3Attribute AS Attribute
        FROM R2Item a, R3Data b, R6TemplateItem c
        WHERE ( a.R2ItemId = @ItemId or a.R2ParentId = @ItemId
            or a.R2ParentId = ( SELECT R2ItemId FROM R2Item WHERE R2ParentId = 0
                and R2GdDdId = ( SELECT R2GdDdId FROM R2Item WHERE R2ItemId = @ItemId))
            or a.R2ItemId = ( SELECT R2ItemId FROM R2Item WHERE R2ParentId = 0
                and R2GdDdId = ( SELECT R2GdDdId FROM R2Item WHERE R2ItemId = @ItemId)))
            and b.R3ItemId = a.R2ItemId
            and a.R2GdDdId = c.R6GdDdId
            and (c.R6ParentId = @TemplateParentId
                or c.R6TemplateItemId = @TemplateParentId)
            and ( b.R3DivisionId = @DivisionId or b.R3DivisionId IS NULL)
            order by c.R6Row, c.R6Col
    END

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
--EXECUTE gd_getItem 74, 165
--GO
select * from R2Item
--select * from R6TemplateItem

Set Item

-- =============================================
-- Create procedure gd_setItem
-- =============================================

-- creating the store procedure
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'gd_setItem'
       AND    type = 'P')
    DROP PROCEDURE gd_setItem
GO

CREATE PROCEDURE gd_setItem
    @ItemId int = NULL,
    @ParentId int = NULL,
    @GdDdId varchar(30) = NULL,
    @Format varchar(50) = NULL,
    @Row int = NULL,
    @Col int = NULL,
    @SeqNo int = NULL,
    @DivisionId int = NULL,
    @Value varchar(250) = NULL,
    @ItemLinkId int = NULL,
    @TaxPackageRef varchar(50) = NULL,
    @ProcessId int = NULL,
    @Attribute int = NULL

AS
SET ROWCOUNT 0
DECLARE @ErrorMsgID int
DECLARE @DataListId int
DECLARE @div int
DECLARE @DataId int

-- A Item conists of two parts 
-- 1, its navigation, specified by the R2Item table
-- 2, its data, specified by the R3Data table
-- There can only ever be one navigation record 
-- There can be many Data records.
-- A Item can be uniquly identified by its @ParentId, @GdDdId, @SeqNo and @DivisionId

BEGIN TRAN T1

    IF @ItemId IS NULL 
    BEGIN
        -- Does a Item exist for this record
        SET @ItemId = ( SELECT R2ItemId 
                        FROM R2Item 
                        WHERE R2ParentId = @ParentId and R2GdDdId = @GdDdId and R2SeqNo = @SeqNo)
    END
                
    IF @ItemId IS NULL or @ItemId = 0 or @ItemId = -1
    BEGIN
        -- Create a new item record
        -- Create a data record

        DECLARE @depth int;
        DECLARE @lineage varchar(100);

        SELECT @depth=R2Depth, @lineage=R2Lineage FROM R2Item where R2ItemId = @ParentId
        SET @depth = @depth + 1;
        SET @lineage = @lineage + LTRIM(Str(@ParentId, 6,0)) + '.'

        INSERT R2Item ( R2ParentId, R2GdDdId, R2Format, R2Row, R2Col, R2SeqNo, R2Depth, R2Lineage)
        VALUES( @ParentId, @GdDdId, @Format, @Row, @Col, @SeqNo, @depth, @lineage)

        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN T1
            RAISERROR ('gd_setItem: Unable to Insert a new Navigation Item',10,1)
            RETURN -1
        END

        SET @ItemId = @@IDENTITY;

        INSERT R3Data( R3ItemId, R3DivisionId, R3Value, R3ItemLinkId, R3TaxPackageRef, R3ProcessId, R3Attribute)
        VALUES( @ItemId, @DivisionId, @Value, @ItemLinkId, @TaxPackageRef, @ProcessId, @Attribute)
        
        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN T1
            RAISERROR ('gd_setItem: Unable to Insert a new Data Item',10,1)
            RETURN -1
        END


        -- Update Depth and lineage
        IF (SELECT COUNT( R2ItemId) FROM R2Item) = 1
        BEGIN
            UPDATE R2Item
            SET R2Depth = 0 , R2Lineage = '.'
            WHERE R2ItemId = 1
        END
    END
    ELSE IF @ItemId IS NOT NULL
    BEGIN
        -- Update the item record

        UPDATE R2Item
        SET R2ParentId = @ParentId
            , R2GdDdId = @GdDdId
            , R2Format = @Format
            , R2Row = @Row
            , R2Col = @Col
            , R2SeqNo = @SeqNo
        WHERE R2ItemId = @ItemId

        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN T1
            RAISERROR ('gd_setItem: Unable to Update a Item',10,1)
            RETURN -1
        END
 
        -- Does the data record exist

        IF @DivisionId IS NULL
        BEGIN
            SET @DataId = ( SELECT R3DataId 
                            FROM R3Data
                            WHERE R3ItemId = @ItemId and R3DivisionId is null)
        END
        ELSE
        BEGIN
            SET @DataId = ( SELECT R3DataId 
                            FROM R3Data
                            WHERE R3ItemId = @ItemId and R3DivisionId = @DivisionId)
        END

        IF @DataId IS NULL
        BEGIN
            -- Create a new Data Record

            INSERT R3Data( R3ItemId, R3DivisionId, R3Value, R3ItemLinkId, R3ProcessId, R3Attribute)
            VALUES( @ItemId, @DivisionId, @Value, @ItemLinkId, @ProcessId, @Attribute)

            SET @ErrorMsgID =@@ERROR
            IF @ErrorMsgID <>0
            BEGIN
                ROLLBACK TRAN T1
                RAISERROR ('gd_setItem: Unable to Insert a new Data Item',10,1)
                RETURN -1
            END
        END
        ELSE
        BEGIN
            -- Update the Data Record

            UPDATE R3Data
            SET R3DivisionId = @DivisionId
                , R3Value = @Value
                , R3ItemLinkId = @ItemLinkId
                , R3TaxPackageRef = @TaxPackageRef
                , R3ProcessId = @ProcessId
                , R3Attribute = @Attribute
            WHERE R3DataId = @DataId

            SET @ErrorMsgID =@@ERROR
            IF @ErrorMsgID <>0
            BEGIN
                ROLLBACK TRAN T1
                RAISERROR ('gd_setItem: Unable to Update a Data Item',10,1)
                RETURN -1
            END
        END
    END
    ELSE
    BEGIN
        ROLLBACK TRAN T1
        RAISERROR ('gd_setItem: Should not of got here',10,1)
        RETURN -1
    END

    COMMIT TRAN T1
    RETURN @ItemId
GO

Delete Item

-- =============================================
-- Create procedure gd_delItem
-- =============================================

-- creating the store procedure
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'gd_delItem' 
       AND    type = 'P')
    DROP PROCEDURE gd_delItem
GO


CREATE PROCEDURE gd_delItem 
    @ItemId int = NULL
AS
    DECLARE @ErrorMsgID int

    -- Delete a item and all the rows that refer to it.

    BEGIN TRAN T1
        
        -- Delete children of this Item
        DELETE 
        FROM R2Item 
        WHERE R2Lineage like '%.'+cast( @ItemId AS varchar(10)) +'.%'
        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN T1
            RAISERROR ('gd_delItem: Unable to Delete Item',10,1)
            RETURN -1
        END

        -- Clear any links to this Item
        UPDATE R3Data
        SET R3ItemLinkId = NULL
        WHERE R3ItemLinkId = @ItemId


        -- Delete this Item
        DELETE 
        FROM R2Item
        WHERE R2ItemId = @ItemId
        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN T1
            RAISERROR ('gd_delItem: Unable to Delete Item',10,1)
            RETURN -1
        END

    COMMIT TRAN T1

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
--gd_delItem 13

Delete Item Child

-- =============================================
-- Create procedure gd_delItemChild
-- =============================================

-- creating the store procedure
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'gd_delItemChild' 
       AND    type = 'P')
    DROP PROCEDURE gd_delItemChild
GO


CREATE PROCEDURE gd_delItemChild 
    @ItemId int = NULL
AS
    DECLARE @ErrorMsgID int

    -- Delete a item and all the rows that refer to it.

    BEGIN TRAN T1

        DELETE
        FROM R2Item 
        WHERE R2ParentId = @ItemId

        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN T1
            RAISERROR ('gd_delItem: Unable to Delete Items Children',10,1)
            RETURN -1
        END

    COMMIT TRAN T1

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
--gd_delItem 10067

Get TemplateItem

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'gd_getTemplateItem' 
       AND    type = 'P')
    DROP PROCEDURE gd_getTemplateItem
GO

CREATE PROCEDURE gd_getTemplateItem 
    @ItemId int = NULL
AS

    IF @ItemId IS NOT NULL
    BEGIN
        SELECT a.R6TemplateItemId AS ItemId, a.R6ParentId AS ParentId, a.R6GdDdId AS GdDdId,
            a.R6Format AS Format, a.R6Row AS Row, a.R6Col AS Col, a.R6SeqNo AS SeqNo,
            a.R6Value AS Value, a.R6ItemLinkId AS ItemLinkId
        FROM R6TemplateItem a
        WHERE (a.R6TemplateItemId = ( SELECT R6TemplateItemId FROM R6TemplateItem WHERE R6ParentId = 0 
                and R6GdDdId = ( SELECT R2GdDdId FROM R2Item WHERE R2ItemId = @ItemId))
            or a.R6ParentId = ( SELECT R6TemplateItemId FROM R6TemplateItem WHERE R6ParentId = 0 
                and R6GdDdId = ( SELECT R2GdDdId FROM R2Item WHERE R2ItemId = @ItemId)))
        ORDER BY a.R6ParentId, a.R6Row, a.R6Col
    END
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE gd_getTemplateItem 10
GO

Get Data Item

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'gd_getDataItem'
       AND    type = 'P')
    DROP PROCEDURE gd_getDataItem
GO

CREATE PROCEDURE gd_getDataItem
    @ItemId int = NULL,
    @DivisionId int = NULL
AS


    IF @ItemId IS NOT NULL and @DivisionId IS NULL
    BEGIN
        SELECT a.R2ItemId AS ItemId,
            a.R2ParentId AS ParentId,
            a.R2GdDdId AS GdDdId,
            a.R2Format AS Format,
            a.R2Row AS Row,
            a.R2Col AS Col,
            a.R2SeqNo AS SeqNo,
        a.R2Depth AS Depth,
            b.R3DivisionId AS DivisionId,
            b.R3Value AS Value,
            b.R3ItemLinkId AS ItemLinkId,
            b.R3TaxPackageRef AS TaxPackageRef,
            b.R3ProcessId AS ProcessId,
            b.R3Attribute AS Attribute,
            b.R3DataId AS DataId
        FROM R2Item a, R3Data b
        WHERE (a.R2ItemId = @ItemId or a.R2ParentId = @ItemId)
        and b.R3ItemId = a.R2ItemId
    and (b.R3DivisionId IS NULL
    or b.R3DivisionId = (
            SELECT R5DivisionId
            FROM R5Division
            WHERE b.R3DivisionId IS NOT NULL
            and R5DivisionId = b.R3DivisionId
            and R5Deleted IS NULL))
        ORDER BY a.R2ParentId,a.R2GdDdId,a.R2SeqNo
    END
    ELSE IF @ItemId IS NOT NULL and @DivisionId IS NOT NULL
    BEGIN
    -- Make sure its not marked as deleted.
    IF( SELECT R5DivisionId FROM R5Division WHERE R5DivisionId = @DivisionId and R5Deleted IS NULL) > 0
    BEGIN
            SELECT a.R2ItemId AS ItemId,
                a.R2ParentId AS ParentId,
                b.R3DataId AS DataId,
                a.R2GdDdId AS GdDdId,
                a.R2Format AS Format,
                a.R2Row AS Row,
                a.R2Col AS Col,
                a.R2SeqNo AS SeqNo,
                a.R2Depth AS Depth,
                b.R3DivisionId AS DivisionId,
                b.R3Value AS Value,
                b.R3ItemLinkId AS ItemLinkId,
                b.R3TaxPackageRef AS TaxPackageRef,
                b.R3ProcessId AS ProcessId,
                b.R3Attribute AS Attribute,
                b.R3DataId AS DataId
               FROM R2Item a, R3Data b
            WHERE (a.R2ItemId = @ItemId or a.R2ParentId = @ItemId)
            and b.R3ItemId = a.R2ItemId
        and (b.R3DivisionId = @DivisionId or b.R3DivisionId IS NULL)
            ORDER BY a.R2ParentId,a.R2GdDdId,a.R2SeqNo
    END
    END

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE gd_getDataItem 1803, 167
GO

Set Template Item

-- =============================================
-- Create procedure gd_setTemplateItem
-- =============================================

-- creating the store procedure
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'gd_setTemplateItem' 
       AND    type = 'P')
    DROP PROCEDURE gd_setTemplateItem
GO

CREATE PROCEDURE gd_setTemplateItem 
    @ItemId int = NULL,
    @ParentId int = NULL,
    @GdDdId varchar(30) = NULL,
    @Format varchar(50) = NULL,
    @Row int = NULL, 
    @Col int = NULL,
    @SeqNo int = NULL,
    @DivisionId int = NULL,
    @Value varchar(50) = NULL,
    @ItemLinkId int = NULL
AS
    SET ROWCOUNT 0
    DECLARE @ErrorMsgID int
    DECLARE @DataListId int
    DECLARE @div int

    BEGIN TRAN Template

    IF @ItemId IS NULL
    BEGIN
        INSERT R6TemplateItem ( R6ParentId, R6GdDdId, R6Format, R6Row, R6Col, R6SeqNo, R6Value, R6ItemLinkId ) 
        VALUES( @ParentId, @GdDdId, @Format, @Row, @Col, @SeqNo, @Value, @ItemLinkId)

        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN Template
            RAISERROR ('gd_setTemplateItem: Unable to Insert a new Item',10,1)
            RETURN -1
        END

        SET @ItemId = @@IDENTITY;

        -- Update Depth and lineage
        IF (SELECT COUNT( R6TemplateItemId) FROM R6TemplateItem) = 1
        BEGIN
            UPDATE R6TemplateItem
            SET R6Depth = 0 , R6Lineage = '.'
            WHERE R6TemplateItemId = 1
        END

        WHILE EXISTS (SELECT * FROM R6Item WHERE R6Depth IS NULL)
        UPDATE T SET T.R6Depth = P.R6Depth +1, 
                T.R6Lineage = P.R2Lineage + LTRIM(Str(T.R6ParentId, 6,0)) + '.'
        FROM R2Item As T
            INNER JOIN R6Item AS P on (T.R6ParentId=P.R6ItemId)
        WHERE P.R2Depth >=0
            AND P.R6Lineage IS NOT NULL
            AND T.R6Depth IS NULL

    END
    ELSE IF @ItemId IS NOT NULL
    BEGIN
        UPDATE R6TemplateItem
        SET R6ParentId = @ParentId, R6GdDdId = @GdDdId, 
            R6Format = @Format, R6Row = @Row, R6Col = @Col, R6SeqNo = @SeqNo, R6Value = @Value, R6ItemLinkId = @ItemLinkId
        WHERE R6TemplateItemId = @ItemId

        SET @ErrorMsgID =@@ERROR
        IF @ErrorMsgID <>0
        BEGIN
            ROLLBACK TRAN Template
            RAISERROR ('gd_setTemplateItem: Unable to Update a Item',10,1)
            RETURN -1
        END
    

    END
    ELSE
    BEGIN
        ROLLBACK TRAN Template
        RAISERROR ('gd_setTemplateItem: Should not of got here',10,1)
        RETURN -1
    END

    COMMIT TRAN Template

    -- SET the OUTPUT Parameter
--  SET @RTN = @ItemId;

--  EXECUTE gd_getTemplateItem @ItemId;
    RETURN @ItemId

GO

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
--gd_setTemplateItem @ParentId = 1, @GdDdId = 'fred', @Format = 'xxx', @Row = 3, @Col = 2, @Value = '54'

--gd_getItem 10001
--gd_getTemplateItem 25990
--select * from gd_vItem

-- set a new template item
-- gd_setTemplateItem @ParentId = NULL, @GdDdId = '', @Format = '', @Row= NULL, @Col = NULL, @Value = NULL

-- update a template item
-- gd_setTemplateItem @ItemId = NULL, @ParentId = NULL, @GdDdId = '', @Format = '', @Row= NULL, @Col = NULL, @Value = NULL

View Data Item

-- =============================================
-- Create view gd_vDataItem
-- =============================================
IF EXISTS (SELECT TABLE_NAME
       FROM   INFORMATION_SCHEMA.VIEWS
       WHERE  TABLE_NAME = N'gd_vDataItem')
    DROP VIEW gd_vDataItem
GO

CREATE VIEW gd_vDataItem
AS
    SELECT a.R2ItemId AS ItemId,
        a.R2ParentId AS ParentId,
        a.R2GdDdId AS GdDdId,
        a.R2Depth AS Depth,
        c.R6Format AS Format,
        c.R6Row AS Row,
        c.R6Col AS Col,
        a.R2SeqNo AS SeqNo,
        c.R6SeqNo AS TemplateSeqNo,
        b.R3DivisionId AS DivisionId,
        b.R3Value AS Value,
        b.R3ItemLinkId AS ItemLinkId,
        b.R3TaxPackageRef AS TaxPackageRef,
        b.R3ProcessId AS ProcessId,
        b.R3Attribute AS Attribute
    FROM R2Item a, R3Data b, R6TemplateItem c
    WHERE a.R2ItemId = b.R3ItemId
        and a.R2GdDdId = c.R6GdDdId

GO
select * from gd_vDataItem

View Template Item

-- =============================================
-- Create view gd_vTemplateItem
-- =============================================
IF EXISTS (SELECT TABLE_NAME 
       FROM   INFORMATION_SCHEMA.VIEWS 
       WHERE  TABLE_NAME = N'gd_vTemplateItem')
    DROP VIEW gd_vTemplateItem
GO

CREATE VIEW gd_vTemplateItem
AS 
    SELECT R6TemplateItemId AS ItemId
        , R6ParentId AS ParentId
        , R6GdDdId AS GdDdId
        , R6Depth AS Depth
        , R6Lineage AS Lineage
        , R6Format AS Format
        , R6Row AS Row
        , R6Col AS Col
        , R6Value AS Value
        , R6SeqNo AS SeqNo
        , R6ItemLinkId AS ItemLinkId
    FROM R6TemplateItem ti


GO
select * from gd_vTemplateItem

View Template Tree

-- =============================================
-- Create view gd_vTemplateTree
-- =============================================
IF EXISTS (SELECT TABLE_NAME 
       FROM   INFORMATION_SCHEMA.VIEWS 
       WHERE  TABLE_NAME = N'gd_vTemplateTree')
    DROP VIEW gd_vTemplateTree
GO

CREATE VIEW gd_vTemplateTree
AS 
    SELECT R6TemplateItemId AS ItemId
        , R6ParentId AS ParentId
        , R6GdDdId AS GdDdId
        , R6Depth AS Depth
        , R6Lineage AS Lineage
        , R6Format AS Format
        , R6Row AS Row
        , R6Col AS Col
        , R6Value AS Value
        , R6SeqNo AS SeqNo
        , R6ItemLinkId AS ItemLinkId
    FROM R6TemplateItem ti
    WHERE R6ParentId = 0

GO
select * from gd_vTemplateTree