When defining a custom search using SQL, it would be useful to be able to use a Common Table Expression (CTE) when using SQL Server. Currently the tool does not throw an error, but does not execute a statement that begins with a CTE. Here is an example query that would build a path to the current object by using a recursive CTE to find the Parents of the object.
;With Hierarchy (Object_ID,Name,ParentID,Path)
AS
(
--Anchor query defines the first non-recursive query
SELECT p.Object_ID,p.Name,p.ParentID, CAST(p.Name AS varchar(max)) as Path
FROM t_object p
LEFT JOIN t_object t
ON t.ParentID = p.Object_ID
WHERE p.ParentID = 0
UNION ALL
--Recursive query
SELECT t2.Object_ID,t2.Name,t2.ParentID, CAST(h.Path + '->' + t2.Name AS varchar(max))
FROM Hierarchy h
INNER JOIN t_object t2
ON t2.ParentID = h.Object_ID
)
--output query using the CTE defined above
SELECT distinct Object_ID, Name,ParentID, Path
FROM Hierarchy where ParentID <> 0
OPTION (MAXRECURSION 0)
