SQL with dynamic clause where parameter

Question

I have a tree database with the following structure:

Table Fields:

NodeID int ParentID int Name varchar(40) TreeLevel int 

I would like to use the @NodeID variable in the first part of the with clause, so as not to get the whole table, starting from the part that interests me (see where Parent=@ParentID and comment).

 with RecursionTest (NodeID,ParentID,ThemeName) as ( --if i remove the where from here it spends too much time (the tree is big)-- select Nodeid,ParentID,Name from TreeTable where ParentID=@ParentID union all select T0.Nodeid, T0.ParentID, T0.Name from TreeTable T0 inner join RecursionTest as R on T0.ParentID = R.NodeID ) select * from RecursionTest 

This causes some errors, but my question is:

• Can I pass a variable to a with clause?

Thank you very much in advance.

Sincerely.

Jose

Answer 1

Yes.

 declare @ParentID int set @ParentID = 10; with RecursionTest (NodeID,ParentID,ThemeName) .... 

You can wrap it all in a parameterized built-in TVF. An example of this latter approach.

 CREATE FUNCTION dbo.RecursionTest (@ParentId INT) RETURNS TABLE AS RETURN ( WITH RecursionTest (NodeID,ParentID,ThemeName) AS ( /*... CTE definition goes here*/ ) SELECT NodeID,ParentID,ThemeName FROM RecursionTest ) GO SELECT NodeID,ParentID,ThemeName FROM dbo.RecursionTest(10) OPTION (MAXRECURSION 0) 

Answer 2

Unfortunately, <11g this will cause the aliases ORA-32033 - an unsupported column alias, as this functionality is not supported <this version