enrique_aeo
asked on
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.GetDescen
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
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.GetDescen
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
ASKER
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
without seeing the functions' code, it will be impossible to tell what it's doing
ASKER
i do not understand the function getdescendant
you did not post the functions's code, so I cannot tell you neither
ASKER
this function is own the data type hierarchyid
sorry, I don't understand that?
unless the data type is XML, eventually?
unless the data type is XML, eventually?
ASKER
datatype is hierarchyid. This datatype is develop in .net
well, the functions are described there, so what is the issue?
I mean, what exactly are you not understanding?
I mean, what exactly are you not understanding?
ASKER
sorry, i have a sick
I have read but not understand, is why I ask your help
I have read but not understand, is why I ask your help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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 ...