Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

2778a datatype hierarchyid - GetDescendant

Hi experts, i have this store procedure
CREATE PROC AddTerritory(@mgrid int, @territoryid int, @t_name varchar(50))
AS
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode
   FROM Sales.SalesTerritoryOrg
   WHERE TerritoryID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode)
      FROM Sales.SalesTerritoryOrg
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT INTO Sales.SalesTerritoryOrg (OrgNode, TerritoryID, TerritoryName)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @territoryid, @t_name)
   COMMIT
END ;

but i do not understand this code
SELECT @mOrgNode = OrgNode
   FROM Sales.SalesTerritoryOrg
   WHERE TerritoryID = @mgrid

and

   SELECT @lc = max(OrgNode)
      FROM Sales.SalesTerritoryOrg
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

can you help me
0
enrique_aeo
Asked:
enrique_aeo
  • 6
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but i do not understand this code
>SELECT @mOrgNode = OrgNode
 >  FROM Sales.SalesTerritoryOrg
 >  WHERE TerritoryID = @mgrid

this fetches the value of OrgNode, based on the select, with @mgrid as parameter (input value) into the variable @mOrgNode.

the second query is doing similarly, only that the query is using a function in the where clause which is a bit surprising to me as the function does not use any parameter itself ...
0
 
enrique_aeoAuthor Commented:
this is the code
CREATE TABLE Sales.SalesTerritoryOrg
(OrgNode hierarchyid PRIMARY KEY CLUSTERED,
OrgLevel AS OrgNode.GetLevel(), --Devuelve un entero que representa la profundidad del nodo this en el árbol
TerritoryID int UNIQUE NOT NULL,
TerritoryName varchar(50) NOT NULL
);
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
same issue, without parameters such functions will return a "constant" value ... normally. ..
without seeing the functions' code, it will be impossible to tell what it's doing
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
enrique_aeoAuthor Commented:
i do not understand the function getdescendant
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you did not post the functions's code, so I cannot tell you neither
0
 
enrique_aeoAuthor Commented:
this function is own the data type hierarchyid
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I don't understand that?
unless the data type is XML, eventually?
0
 
enrique_aeoAuthor Commented:
datatype  is hierarchyid. This datatype is develop in .net
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, the functions are described there, so what is the issue?
I mean, what exactly are you not understanding?
0
 
enrique_aeoAuthor Commented:
sorry, i have a sick
I have read but not understand, is why I ask your help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
still, what exactly do you not understand?

but i do not understand this code
SELECT @mOrgNode = OrgNode
   FROM Sales.SalesTerritoryOrg
   WHERE TerritoryID = @mgrid

I mean, there is no rocket science there?


   SELECT @lc = max(OrgNode)
      FROM Sales.SalesTerritoryOrg
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

the only thing that is "not obvious" is the WHERE clause.
run this query to see the "data" returned ...
   SELECT OrgNode,OrgNode.GetAncestor(1) anc
      FROM Sales.SalesTerritoryOrg
  ORDER BY OrgNode.GetAncestor(1) , OrgNode desc

Open in new window

and you will see what the above query will return, for a given value of "anc"

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now