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

Jonathan Brotto 1,076 Reputation points
2021-12-15T20:47:09.277+00:00

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
Transact-SQL
Transact-SQL
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
    2021-12-16T08:21:18.517+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 98,731 Reputation points
    2021-12-15T22:48:16.87+00:00

    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
    2021-12-16T06:58:06.727+00:00

    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
    
    0 comments No comments