SQL with clause dynamic where parameter

Question

I have a tree-style database with the following structure:

Table fields:

NodeID int 
ParentID int
Name varchar(40)
TreeLevel int

I would like to use a variable @NodeID in the first part of the with clause to don't get all the table just start from the piece I'm interested in (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 throws some errors, but my question is:

• Is possible to pass a variable to a with clause ?

Thanks a lot in advance.

Best regards.

Jose

Answer 1

Yes.

declare @ParentID int
set @ParentID = 10;

with RecursionTest (NodeID,ParentID,ThemeName) ....

You could wrap the whole thing up in a parameterised inline TVF as well. Example of this last 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)