Invalid Object Problem dynamic(CTE) Paging

  • Hi there,

    I'm new to T-SQL and i'm trying to implement the efficient paging method outlined in scottgu's article: http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

    The problem is I'm trying to make the stored procedure Dynamic, but when I run the following Stored procedure I get the following error:

    Incorrect syntax near ')'.

    Invalid object name 'ItemEntries'.

    It looks like it is to do with the common table expression "ItemEntries". What would be the correct syntax for the following dynamic sql:

    SET @sqlString = 'With ItemEntries as (

    SELECT ROW_NUMBER() OVER (ORDER BY price ASC) as Row, dogID

    FROM tblDogs

    WHERE ' + @whereClause + ')'

    EXEC(@sqlString)

    Any help would be very much appreciated, thanks

  • As Matt Miller noted in the other thread, it would help if you'd show us the code that populates the @whereClause variable. At a glance, it appears that the likely culprit can be found there.

  • He is all over the places so this must be reallt important, having twice the number of helpers...

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88706

    I suggested adding a ";" before the WITH keyword 

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Hi Guys thanks for the replies.

    Peter you're right I am all over the place! Been at this all day and it is driving me insane lol can't find anything on google either :o(

    I've used the step in facility in Visual Studio and these are the values of the variables:

    @whereClause = tblDogs.dogCode = 'LAB'

    @sqlb = With ItemEntries as (SELECT ROW_NUMBER() OVER (ORDER BY dogID ASC) as Row, dogID FROM tblDogs WHERE tblDogs.dogCode = 'LAB')

    Here is the full SP:

    ALTER PROCEDURE [dbo].[spPropertyGetPagedPropertiesFromSearch]

    @whereClause varchar(1000),

    @orderByClause varchar(1000),

    @pageIndex INT,

    @numRows INT,

    @itemCount INT OUTPUT

    AS

    BEGIN

    DECLARE @sqla nvarchar(1000);

    DECLARE @parms nvarchar(100);

    DECLARE @sqlb nvarchar(1000);

    SET @sqla ='SELECT @itemCount2=(SELECT COUNT(*) FROM tblDogs WHERE ' + @whereClause + ')'

    SET @parms = '@itemCount2 int OUTPUT'

    exec sp_executesql @sqla, @parms, @itemCount2 = @itemCount OUTPUT

    /*

    The below statements use the new ROW_NUMBER() function in SQL 2005 to return only the specified

    rows we want to retrieve from the Products table

    */

    Declare @startRowIndex INT;

    set @startRowIndex = (@pageIndex * @numRows) + 1;

    SET @sqlb = 'With ItemEntries as (SELECT ROW_NUMBER() OVER (ORDER BY price ASC) as Row, dogID FROM tblDogs WHERE ' + @whereClause + ')'

    EXEC(@sqlb)

    Select dogID

    FROM ItemEntries

    WHERE Row between

    @startRowIndex and @StartRowIndex+@NumRows-1

    END

  • the issue is the @where clause

    @whereclause='tbldogs.dogcode=''LAB'''

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hi Matt,

    Cheers for the reply. Not too sure what you mean??... If I hard-code this in to the SP it runs fine:

    With ItemEntries as (SELECT ROW_NUMBER() OVER (ORDER BY dogID ASC) as Row, dogID FROM tblDogs WHERE tblDogs.dogCode = 'LAB')

    And when I check the variable data in visual studio that is the exact string that is being held, and therefore executed.

    Any other ideas?

    Thanks again :o)

  • AT LAST I FIGURED IT OUT!!

    if anybody else is interested...

    when executing a common table expression (CTE) you must also include the statement that immediately follows it and uses the CTE e.g.

    Declare @startRowIndex INT;

    Declare @endRowIndex INT;

    set @startRowIndex = (@pageIndex * @numRows) + 1;

    set @endRowIndex = (@StartRowIndex+@NumRows-1);

    EXEC('WITH ItemEntries AS (SELECT ROW_NUMBER() OVER (ORDER BY dogID ASC) AS Row, dogID FROM tblDogs WHERE ' + @whereClause + ')

    Select dogID

    FROM ItemEntries

    WHERE Row between ' + @startRowIndex + ' and ' + @endRowIndex);

  • By the way - for better or for worse - Although I can see why you'd want to do this - having the whereclause be something getting passed in sets this up for a textbook SQL Injection attack.  If you're using this under any circumstance that gets feedback to build that from user input - BE CAREFUL.  Validate the CRAP out of that thing.

    As to what I meant - the value you showed wasn't appropriately quoted out, so (as it appeared when you posted it) would have errored out.

    that being - good for you to have found your issue.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply