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

Jonathan Brotto 1,076 Reputation points

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,247 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,036 Reputation points

    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,

    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.

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 98,731 Reputation points

    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.

    0 comments No comments

  2. Ronen Ariely 15,081 Reputation points

    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:

    INSERT T(ID) VALUES (2),(23),(32)
    SET @ID = 2
    ;With MyCTE as (
        select ID from T
        WHERE ID > @ID
    0 comments No comments