Is it possible to use a paramter in the with clause?

Question

I was wondering as I using the with clause to prefetch data and nest the results in my query. Within the were clause could I use parameter?

Answer 1

Hi，@Jonathan Brotto

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  
SET @val=6000  
;WITH CTE AS  
(  
SELECT employee_id,first_name,last_name,salary,department_id  
FROM employees  
WHERE salary>@val  
)  
SELECT employee_id,first_name,last_name,salary FROM CTE  
WHERE department_id=10  

For more info about Features and Limitations of CTE,please refer to the Document.

Best regards,
LiHong

---

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.

Answer 2

Your question is not very clear, but yes, you can refer to variables in a Common Table Expression (CTE), which I assume that you have in mind. (The first CTE in a query is introduced with WITH.)

However, you may have a misconception about CTEs (that is not uncommon). I sense that when you talk about "prefetch".

A CTE is a logical construct. A CTE may never be computed as such, but the optimizer may recast the computation order. Here is an example that I use in one my presentations:

WITH OrderAmounts AS (
   SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
   FROM   dbo.[Order Details] OD
   GROUP  BY OD.OrderID
)
SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   OrderAmounts OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;

There are one million orders in the database, and logically the CTE says compute the amount for all orders in the database. But the optimizer will realise that the it only needs to compute the orders on that particular date in the query, and the query when I demo it is instant.

Answer 3

Is it possible to use a paramter in the with clause?

The name is common table expression or in short CTE

The answer is yes

Important! If you want to have statements before the CTE configuration (before the "WITH") then you must use Semicolon (the character ;) before the word WITH or you will get the error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Here is a full demo:

DROP TABLE IF EXISTS T
GO
CREATE TABLE T(ID INT)
GO
INSERT T(ID) VALUES (2),(23),(32)
GO

------------------- 
DECLARE @ID INT 
SET @ID = 2
;With MyCTE as (
    select ID from T
    WHERE ID > @ID
)
SELECT * FROM MyCTE
GO