SELECT from multiple cte with dynamic WHERE condition

Question

I have a stored procedure which returns a list of records.

In one case (type=1), this will return the top 10 records based on page number.

In other case (type=2), this will return the all records.

The columns selected are same in both cases.

I am using 2 cte's to get the data required.

How can I implement the dynamic part which determines no. of records selected; with minimum performance impact.

Code blueprint

with cte1 as 
(
    SELECT ....
),
cte2 as 
(
    SELECT ....
)
SELECT 
    ROW_NUMBER() OVER ( ORDER BY Col1) AS RowId,
    cte1.*, cte2.PlanName
FROM 
    cte1 
INNER JOIN
    cte2 ON cte2.Id = cte1.Id 
WHERE
    .....\*different code here*\

Thanks

Answer 1

You can put a WHERE condition like where (RowId <=10 and @case=1) or @case=2

Your query should look like following.

SELECT * 
FROM   (SELECT Row_number() 
                 OVER ( 
                   ORDER BY col1) AS RowId, 
               cte1.*, 
               cte2.planname 
        FROM   cte1 
               INNER JOIN cte2 
                       ON cte2.id = cte1.id)t 
WHERE  ( rowid <= 10 
         AND @case = 1 ) 
        OR @case = 2 

If case=1 above code will return only 10 rows based on your row_number() else it will return all.