SQL exec runs error after using WITH Statement

Question

Hi, I just get a problem executing my sql statement, This is the Northwind db.

    declare @PageIndex int
    declare @PageSize int
    declare @PageLowerBound int
    declare @PageUpperBound int
    declare @sql nvarchar(4000)

    select @PageIndex = 3
    select @PageSize = 5

    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1
    with cts
    as
    (
    SELECT *
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS ROW,* FROM Orders)
    AS Orders  WHERE ROW >=@PageLowerBound AND Row<=@PageUpperBound
    )
   select @sql = @sql + 'select * from cte' 
    exec (@sql)

(Modified) Just modify a little bit, I use select * from cte, I accidently pasted the wrong code. Sorry. Actually, i am trying to append a where clause in @sql and execute it, but it throws a error message,saying "Incorrect syntax near the keyword 'exec'." What did I do wrong? Thank you

Answer 1

Semi colon you need right before the with cts You need a semi-colon when you express a CTE (that is right before it).

 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
    ;with cts
    as
    (
    SELECT *
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS ROW,* FROM Orders)
    AS Orders  WHERE ROW >=@PageLowerBound AND Row<=@PageUpperBound
    )
   select @sql = @sql + 'select * from orders' 
    exec (@sql)

You also did not use the CTE, so why do you have it ?

Answer 2

What you'll eventually see is:

  Common table expression defined but not used.

Consume your CTE in the very next statement, or don't create it at all.

There's another lingering problem here in your example:

 select @sql = @sql + 'select * from orders' 

This is effectively the same as:

  select @sql = NULL + 'select * from orders' 

Assigning NULL and any value to a variable will ensure that it's always NULL.

Perhaps you've obfuscated your code, but ensure that @sql actually has a non-NULL value before trying to execute that dynamic SQL.

Remember that your dynamic SQL will be run in its own scope, and won't have any reference to your CTE or other variables.

Answer 3

I'm guessing you are trying to make a standard CTE paging pattern (e.g. as described here) and make it dynamic for a pasted WHERE clause?

AFAIK you will also need to add the CTE into the sql string (exec or sp_executesql) in order to achieve this

Answer 4

You need to use:

DECLARE @sql NVARCHAR(4000)

    SET @sql = 'with cts as (
                   SELECT *
                     FROM (SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS ROW, 
                                  o.* 
                             FROM ORDERS o) AS Orders  
                    WHERE ROW >= @PageLowerBound 
                      AND Row <= @PageUpperBound)
                SELECT * 
                  FROM cte ' 

BEGIN

  EXEC sp_executesql @sql, N'@PageLowerBound INT, @PageUpperBound INT',
                           @PageLowerBound, @PageUpperBound

END