title: How can i pass parameters to a Common Table Expression inside a sp?
body: <p>I want to rewrite a functioning, but slow stored procedure using CTE (common table Expression)</p>
  
  <p>I have a big stored procedure where i build the nececary SQL dynamicaly based on parameter used. Currently there are 27 parameters, i compose the SQL string that i execute at the end it looks like:</p>
  
  <pre><code>    DECLARE @SqlWhereClause NVARCHAR(MAX)  
  SET @SqlWhereClause = ' WHERE ([InTimeStamp] BETWEEN ''' + CONVERT(VARCHAR(19), @fromDate, 120) + ''' AND ''' + CONVERT(VARCHAR(19), @toDate, 120) + ''')'
  
  IF @showOnlyErrors = '1'  
     BEGIN
       SET @SqlWhereClause += ' AND Status = ''Error'''  
      END
  
  IF LEN(LTRIM(RTRIM(@docNo))) &gt; 0  
    BEGIN  
      IF @matchExact = '1'   
        BEGIN
           SET @SqlWhereClause += ' AND DocumentNumber = ''' + @docNo + ''''  
        END
     ELSE
      BEGIN   
          SET @SqlWhereClause += ' AND (contains([DocumentNumber],'''+ @docNo +'''))'
      END 
    END     
  </code></pre>
  
  <p>At the end, i add the pagination and transform it to the final formSQL:</p>
  
  <pre><code>    IF CONVERT(int, LTRIM(RTRIM(@takeRows))) &gt; 0   
     BEGIN
       SET @SqlOrderByClause += ' OFFSET ' + @rowNumberToSkip +' ROWS FETCH NEXT '+ @takeRows +' ROWS ONLY '
       Set @RowCount = ' Select @totalRecords = count(1) from  dbo.Messages WITH (NOLOCK) ' + @SqlWhereClause
     END
  
      SET @SQL = @SqlSelect + @SqlFrom + @SqlWhereClause + @SqlOrderByClause + ' ; '  + @RowCount  
      PRINT @SQL  
      EXECUTE sp_executesql @SQL, @params, @totalRecords OUTPUT 
  </code></pre>
  
  <p>Everithing is working like a charm. No problems. Only performance problems. To solve one of it, i would try to use CTE (common table extpression)</p>
  
  <p>But this is not working:</p>
  
  <pre><code>   With DataSQL AS
      (@SqlSelect + @SqlFrom + @SqlWhereClause + @SqlOrderByClause),
  
     - incorrect syntax near @SqlSelect - Expecting '(' or Select.
  </code></pre>
  
  <p>I also tried this one:</p>
  
  <pre><code>     WITH DataSQL AS 
       ( Select @SqlSelect    From @SqlFromFast   
      Where @SqlWhereClause   Order By @SqlOrderByClause),
  </code></pre>
  
  <p>here i get: </p>
  
  <pre><code>    An expression of non-boolean type specified in a context where a condition is expected, near 'Order'
  </code></pre>
  
  <p>Any idea? Or is it not possible to use CTE with multiple variables? All i found until now are simply queries with one, maybe two variables.</p>