Invalid Object Problem dynamic(CTE)

Question

Invalid Object Problem dynamic(CTE)

at work

Old Hand

Points: 371
More actions
August 30, 2007 at 1:31 pm

#197537

*** Sorry can this be removed as i've posted in the wrong section
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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

what's in the @whereclause?

----------------------------------------------------------------------------------
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?

David McFarland SSChampion Points: 11815 More actions · Answer 2

Are you using 2005? That code is VERY 2005 specific.

ETA: Ignore, as I see you notice that you posted in the wrong section, so assuming that you do have 2005.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

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"

at work Old Hand Points: 371 More actions · Answer 4

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

at work Old Hand Points: 371 More actions · Answer 5

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);