How to use Common Table Expression with parameters?

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

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

Tags:

Tsql