title: Filtering a CTE in a Stored Procedure
body: <p>I need to filter cte data by procedure parameters- in fact if @agentid is null gives me all data else filter data by @agentid . I tried many ways , I used case in cte and get erros , I used if in ( if @status after cte ) and I get errors to , I used if in cte and get errors</p>
  
  <p>This procedure is for pagination and filter data and in works fine . </p>
  
  <pre><code>USE [HN_PRODUCTION]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[grid_data_pagination]  
      @status int  = null 
      , @offset int = 0 
      , @limit int = 2  
      , @agentid int = null 
      , @start_date date = null 
      , @end_date date = null                         
  AS
  BEGIN         
      IF  @status is  null  
      BEGIN       
              with cte as 
              (
                  Select  FilePath  , ReceptionLocalTime , AgentID , FileDuration   , PhoneNumber , Indice   , Status  , ROW_NUMBER() over (  order by ID ) as RowNumber
                  from dbo.V1_594                 
              )
  
      select FilePath as مسیر ,ReceptionLocalTime as زمان , FileDuration as مدت , PhoneNumber  as تلفن , Indice as اندیس , Status as وضعیت 
      from cte
      where cte.RowNumber &gt; @limit * @offset and cte.RowNumber &lt;= @limit * ( @offset + 1 )                                
      END
      ELSE 
      BEGIN
          with cte as 
          (
              Select  FilePath  , ReceptionLocalTime   , FileDuration   , PhoneNumber , Indice   , Status  , ROW_NUMBER() over (  order by ID ) as RowNumber
              from dbo.V1_594
              WHERE Status = @status 
          )
  
          select FilePath as مسیر ,ReceptionLocalTime as زمان , FileDuration as مدت , PhoneNumber  as تلفن , Indice as اندیس  
          from cte
          where cte.RowNumber &gt; @limit * @offset and cte.RowNumber &lt;= @limit * ( @offset + 1 ) ;
      END
  END
  </code></pre>