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

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