August 30, 2007 at 1:35 pm
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
August 30, 2007 at 1:58 pm
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.
August 30, 2007 at 2:30 pm
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"
August 30, 2007 at 3:04 pm
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
August 30, 2007 at 4:19 pm
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?
August 30, 2007 at 4:52 pm
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)
August 30, 2007 at 5:22 pm
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);
August 31, 2007 at 8:53 am
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