Database

CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50),
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
AS

SELECT @ActorName = dbo.Actor.ActorName,
       @actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
     dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
      and '1' = case when @division = 'North' then dbo.ActorRole.North 
		     when @division = 'South' then dbo.ActorRole.South
		     when @division = 'West' then dbo.ActorRole.West end

<br />
IF OBJECT_ID('dbo.sp_TestWriteToFile') IS NOT NULL<br />
   DROP PROC dbo.sp_TestWriteToFile<br />
GO<br />
CREATE PROCEDURE [dbo].[sp_TestWriteToFile]<br />
AS<br />
   exec master..xp_cmdshell 'echo hello > c:\file.txt'<br />
   exec master..xp_cmdshell 'echo blah de blah > c:\file.txt', no_output<br />
<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
exec sp_TestWriteToFile

<br />
IF OBJECT_ID('dbo.sp_TestWriteToFile') IS NOT NULL<br />
   DROP PROC dbo.sp_TestWriteToFile<br />
GO<br />
CREATE PROCEDURE [dbo].[sp_TestWriteToFile]<br />
AS<br />
   exec master..xp_cmdshell 'echo appended data >> c:\file.txt'<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
exec sp_TestWriteToFile

ID     decendentID    Comments
---------------------------------------------
0      0              -- [Root + Decendents]
0      1
0      2
0      3
0      4
1      1              -- [Child1 + Decendents]
1      3
2      2              -- [Child2 + Decendents]
2      4
3      3              -- [A GrandChild + Decedents]
4      4              -- [Another GrandChild + Decendents]

SELECT * 
FROM DenormalisedHeirarchy 
WHERE decendentID = @decendentID
AND ID = @somethingID

public class MyEntity {<br />
    int id; <br />
     int pid;            // id of the parent entity<br />
     ArrayList children;  // list of children<br />
     String name;<br />
     // other fields<br />
}

CLASS MyEntity TABLE MYENTITY<br />
      PRIMARY_KEY id<br />
      RELATIONSHIP children REFERENCES ChildrenCollection WITH id<br />
;<br />
COLLECTION_CLASS ChildrenCollection COLLECTION_TYPE ARRAYLIST ELEMENT_TYPE MyEntity<br />
     PRIMARY_KEY pid<br />
;

<br />
   String predicate = ....;  // WHERE clause for the top-level objects<br />
   ArrayList entities = njdx.query("MyEntity", predicate, ...);

PKey    Name                                    PKeyOfParent
------------------------------------------------------------
0       Root                                    NULL 
1       Child1                                  0
2       Child2                                  0
3       A GrandChild                            1
4       Another GrandChild                      2
5	A Great GrandChild                      3
6	A Great Great GrandChild                5
7 	One More GrandChild                     3

CREATE FUNCTION GetDescendants (@nID int)  
RETURNS @tDescReturn table (PID int)
AS  
begin
DECLARE @RowsAdded int
declare @tDesc   TABLE (PKey int,PKeyOfParent int, processed tinyint default 0) 

INSERT @tDesc
   SELECT PKey,PKeyOfParent,0
   FROM SelfRefTable 
   WHERE PKey = @nID 

SET @RowsAdded = @@rowcount

WHILE @RowsAdded > 0
   BEGIN
	UPDATE @tDesc
      		SET processed = 1
      		WHERE processed = 0

	INSERT @tDesc
	      SELECT e.PKey, e.PKeyOfParent,0
	      FROM SelfRefTable e, @tDesc r
	      WHERE e.PKeyOfParent =r.PKey and r.processed = 1

      UPDATE @tDesc
	      SET processed = 2
	      WHERE processed = 1

      SET @RowsAdded = @@rowcount

   END

 INSERT @tDescReturn
   SELECT PKey 
   FROM @tDesc
return 
end

CREATE FUNCTION GetAncestors (@nID int)  
RETURNS @tAscReturn table (PID int)
AS  
begin
DECLARE @RowsAdded int
declare @tAsc   TABLE (PKey int,PKeyOfParent int, processed tinyint default 0) 

INSERT @tAsc
   SELECT PKey,PKeyOfParent,0
   FROM SelfRefTable 
   WHERE PKey = @nID 

SET @RowsAdded = @@rowcount

WHILE @RowsAdded > 0
   BEGIN
	UPDATE @tAsc
      		SET processed = 1
      		WHERE processed = 0

	INSERT @tAsc
	      SELECT e.PKey, e.PKeyOfParent,0
	      FROM SelfRefTable e, @tAsc r
	      WHERE e.PKey = r.PKeyOfParent  and r.processed = 1
	
      	UPDATE @tAsc
	      SET processed = 2
	      WHERE processed = 1

	SET @RowsAdded = @@rowcount
   END

 INSERT @tAscReturn
   SELECT PKey 
   FROM @tAsc
return 
end