CREATE PROCEDURE dbo.EggHeadTest
as
set nocount on
declare @RowCnt int
declare @MaxRows int
declare @Email nvarchar(255)
select @RowCnt = 1
/* Records will contain self-referencing relationships */
declare @Sample1 table
(
Sample1ID int Primary key NOT NULL , ParentID int, SiblingOrder int,Description nvarchar(100)
)
/* Child records to Sample1 */
declare @Sample2 table
(
Sample2ID int Primary key NOT NULL , Sample1ID int, SiblingOrder int, Description nvarchar(100)
)
/* Child records to Sample2 */
declare @Sample3 table
(
Sample3ID int Primary key NOT NULL , Sample2ID int, SiblingOrder int, Description nvarchar(100)
)
/* Child records to Sample3 */
declare @Sample4 table
(
Sample4ID int Primary key NOT NULL , Sample3ID int, SiblingOrder int, Description nvarchar(100)
)
/* Start loading of test data */
insert into @Sample1 values(1,1,0,'CEO')
insert into @Sample1 values(2,1,1,'Vice Pres. Marketing')
insert into @Sample1 values(3,1,2,'Vice Pres. Ops-')
insert into @Sample1 values(4,2,1,' Marketing Director - Direct Mail')
insert into @Sample1 values(5,2,2,' Marketing Director - TV')
insert into @Sample1 values(6,1,3,'Vice Pres. - Research')
insert into @Sample1 values(7,4,1,' Human Resources Director')
insert into @Sample1 values(8,4,2,' Some other item')
insert into @Sample1 values(9,6,1,'Research Analyst')
insert into @Sample2 values (1,8,1,' Marketing Analyst')
insert into @Sample2 values (2,8,2,' Marketing Assistant')
insert into @Sample2 values (3,9,1,' Research Assistant 1')
insert into @Sample2 values (4,9,2,' Research Assistant 2')
insert into @Sample2 values (5,9,3,' Research Assistant 3')
insert into @Sample2 values (6,7,1,' Direct Mail Assistant 1')
insert into @Sample2 values (7,7,2,' Direct Mail Assistant 2')
insert into @Sample2 values (8,7,3,' Direct Mail Assistant 3')
insert into @Sample2 values (9,7,4,' Direct Mail Assistant 4')
insert into @Sample3 values (1,9,1,' Employee 1')
insert into @Sample3 values (2,9,2,' Employee 2')
insert into @Sample3 values (3,9,3,' Employee 3')
insert into @Sample3 values (4,9,4,' Employee 4')
insert into @Sample3 values (5,9,5,' Employee 5')
insert into @Sample3 values (6,9,6,' Employee 6')
insert into @Sample3 values (7,9,7,' Employee 7')
insert into @Sample3 values (8,9,8,' Employee 8')
insert into @Sample3 values (9,9,9,' Employee 9')
insert into @Sample4 values (1,1,1,' Contact 1')
insert into @Sample4 values (2,1,2,' Contact 2')
/* End loading of test data */
/* Bring back a Resultset to be loaded into individual DataTable objects.
Create derived columns for the DataRow object's .Select method and
store it in the SQL column. Also store the column name for the ParentColumn.
This value is also used in the DataRow's .Select method query.
*/
SELECT
'SAMPLE1' as NodeType,
Sample1ID,
ParentID,
SiblingOrder,
Description,
'ParentID <> Sample1ID and ParentID=' as SQL,
'Sample1ID' as ParentColumn
FROM @Sample1 as Tree
order by ParentID,SiblingOrder asc
SELECT
'SAMPLE2' as NodeType,
Sample2ID,
Sample1ID,
SiblingOrder,
Description,
'Sample1ID=' as SQL,
'Sample1ID' as ParentColumn
FROM @Sample2 as Tree
Where Sample1ID in (select Sample1ID from @Sample1)
order by Sample1ID,SiblingOrder asc
SELECT
'SAMPLE3' as NodeType,
Sample3ID,
Sample2ID,
SiblingOrder,
Description,
'Sample2ID=' as SQL,
'Sample2ID' as ParentColumn
FROM @Sample3 as Tree
Where Sample2ID in (select Sample2ID from @Sample2)
order by Sample2ID,SiblingOrder asc
SELECT
'SAMPLE4' as NodeType,
Sample4ID,
Sample3ID,
SiblingOrder,
Description,
'Sample3ID=' as SQL,
'Sample3ID' as ParentColumn
FROM @Sample4 as Tree
Where Sample3ID in (select Sample3ID from @Sample3)
order by Sample3ID,SiblingOrder asc
set nocount off
GO
|