If your question is whether you can use parameters in the where clause of definition statement of CTE, then the answer is yes.
Externally defined parameters can be used in CTE as long as they are in the same batch. And the statement before the WITH keyword must end with a semicolon (;)
Please refer to the sample below.
DECLARE @val INT
;WITH CTE AS
SELECT employee_id,first_name,last_name,salary FROM CTE
For more info about Features and Limitations of CTE,please refer to the Document.
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.