Database

VK-Cadec wrote:

when upper(code) = 'TWASH' then (set @t_wash = count(ISNULL(code,0)))

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, ...);