How to use Common Table Expression with parameters?

Question

I have a stored procedure with 2 CTEs. The second CTE has a parameter

WITH path_sequences
AS
(

),
WITH categories
AS
(
    ... WHERE CategoryId = @CategoryId 
    // I dont know how to get this initial parameter inside the CTE 
)

SELECT * FROM path_sequences p
JOIN categories c
ON p.CategoryId = c.CategoryId

The initial parameter that I need to get inside the second TCE is p.CategoryId. How do I do that without having to create another stored procedure to contain the second CTE?

Thanks for helping

Answer 1

You can create table valued function

create function ftCategories (     @CategoryID int ) returns table as return     with categories as (         ... WHERE CategoryId = @CategoryId      )     select Col1, Col2 ...     from categories 

and use it as

SELECT * FROM path_sequences p     cross apply ftCategories(p.CategoryId) c 

Answer 2

I have created simple query using your code. You can use it like -

DECLARE @CategoryId INT SET @CategoryId = 1  ;WITH path_sequences AS ( SELECT 1 CategoryId ), categories AS (     SELECT 1 CategoryId WHERE 1 = @CategoryId )  SELECT * FROM path_sequences p JOIN categories c ON p.CategoryId = c.CategoryId 

Answer 3

This syntax is for External Aliases:

  -- CTES With External Aliases:
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
    AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )

The only way to add parameters is to use scope variables like so:

--Declare a variable:
DECLARE @category INT

WITH 
MyCTE1 (exName1, exName2)
AS
(
    SELECT <SELECT LIST>
    FROM <TABLE LIST>
    --Use the variable as 'a parameter'
    WHERE CategoryId = @CategoryId
)

Answer 4

First remove the second WITH, separate each cte with just a comma. Next you can add parameters like this:

DECLARE @category INT; -- <~~ Parameter outside of CTEs

WITH 
MyCTE1 (col1, col2)  -- <~~ were poorly named param1 and param2 previously
AS
(
    SELECT blah blah
    FROM blah
    WHERE CategoryId = @CategoryId
),
MyCTE2 (col1, col2)  -- <~~ were poorly named param1 and param2 previously
AS
(

)
SELECT *
FROM MyCTE2 
INNER JOIN MyCTE1 ON ...etc....

EDIT (and CLARIFICATION):

I have renamed the columns from param1 and param2 to col1 and col2 (which is what I meant originally).

My example assumes that each SELECT has exactly two columns. The columns are optional if you want to return all of the columns from the underlying query AND those names are unique. If you have more or less columns than what is being SELECTed you will need to specify names.

Here is another example:

Table:

CREATE TABLE Employee
(
    Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    ManagerId INT NULL
)

Fill table with some rows:

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Donald', 'Duck', 5)

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Micky', 'Mouse', 5)

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Daisy', 'Duck', 5)

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Fred', 'Flintstone', 5)

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Darth', 'Vader', null)

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Bugs', 'Bunny', null)

INSERT INTO Employee 
(FirstName, LastName, ManagerId)
VALUES
('Daffy', 'Duck', null)

CTEs:

DECLARE @ManagerId INT = 5;

WITH 
MyCTE1 (col1, col2, col3, col4)
AS
(
    SELECT *
    FROM Employee e 
    WHERE 1=1
    AND e.Id = @ManagerId
 ),
 MyCTE2 (colx, coly, colz, cola)
 AS
 (
    SELECT e.*
    FROM Employee e 
    INNER JOIN MyCTE1 mgr ON mgr.col1 = e.ManagerId
    WHERE 1=1
 )
 SELECT 
   empsWithMgrs.colx,
   empsWithMgrs.coly,
   empsWithMgrs.colz,
   empsWithMgrs.cola      
 FROM MyCTE2 empsWithMgrs

Notice in the CTEs the columns are being aliased. MyCTE1 exposes columns as col1, col2, col3, col4 and MyCTE2 references MyCTE1.col1 when it references it. Notice the final select uses MyCTE2's column names.

Results: