June 21, 2009 at 8:24 pm
Hi,
Im referring to this sql method http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005
I have used the same method for straight forward sql with select and order by and it works. But I have a TSQL, and when i use it with this method, it's giving error.
my original working sql is this :
DECLARE c2 CURSOR FOR
SELECT A.dbo.TVShow.showid FROM A.dbo.TVShow WHERE A.dbo.TVShow.MasterID_fk=510 AND A.dbo.TVShow.ShowOnMobile=1
OPEN c2
declare @showid int
FETCH NEXT FROM c2 INTO @showid
WHILE @@FETCH_STATUS = 0
BEGIN
declare @var int
set @var = @showid
SELECT top 10 (A.dbo.Episodes.EpID),
A.dbo.Episodes.EpName,
A.dbo.Episodes.EpSynopsis,
A.dbo.Episodes.EpDate,
convert(char(5),dateadd(minute, datediff(mi,epstarttime, case when ependtime < epstarttime then dateadd(day, 1, ependtime ) else ependtime end), 0),114) as duration,
A.dbo.Episodes.EpPostedDate,
A.dbo.Video.VideoTitle,
A.dbo.Video.MediaItemID,
convert (varchar, A.dbo.Video.VideoPostedDate, 101) as VideoPostedDate,
A.dbo.Episodes.ShowID_FK,
A.dbo.PhotoBank.PhotoFilepathFull AS MainImage,
PhotoBank_3.PhotoFilepathFull AS ThumbnailImage,
ROW_NUMBER() OVER (ORDER BY A.dbo.Episodes.EpID DESC) AS RowNumber
FROM
A.dbo.Episodes
INNER JOIN
A.dbo.MasterShow ON A.dbo.MasterShow.MasterID=510 LEFT OUTER JOIN
A.dbo.PhotoBank ON A.dbo.MasterShow.MasterMainImage_PhotoIdRef = A.dbo.PhotoBank.PhotoID LEFT OUTER JOIN
A.dbo.PhotoBank AS PhotoBank_1 ON A.dbo.MasterShow.MasterFooterImage_PhotoIdRef = PhotoBank_1.PhotoID
INNER JOIN
A.dbo.Video
ON
A.dbo.Video.videoid =
(SELECT
min(A.dbo.Video.videoid)
FROM
A.dbo.Video
WHERE
A.dbo.Video.epid_ref =A.dbo.Episodes.epid)
LEFT OUTER JOIN A.dbo.PhotoBank AS PhotoBank_3 ON A.dbo.Video.VideoThumb_PhotoIDRef = PhotoBank_3.PhotoID
WHERE
(A.dbo.Episodes.ShowID_FK =630 and A.dbo.Episodes.EpDate < getdate())
ORDER BY A.dbo.Episodes.EpDate Desc
FETCH NEXT FROM c2 INTO @showid
END
CLOSE c2
DEALLOCATE c2
[/code]
when I add in to the paging sql, I did as
[code]
DECLARE@PageSize INT,
@PageNumber INT,
@FirstRow INT,
@LastRow INT
SELECT@PageSize = 5,
@PageNumber = 3
SELECT@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;
WITH Members AS
(
DECLARE c2 CURSOR FOR
SELECT [A].dbo.TVShow.showid FROM [A].dbo.TVShow WHERE [A].dbo.TVShow.MasterID_fk=510 AND [A].dbo.TVShow.ShowOnMobile=1
OPEN c2
declare @showid int
FETCH NEXT FROM c2 INTO @showid
WHILE @@FETCH_STATUS = 0
BEGIN
declare @var int
set @var = @showid
SELECT top 10 ([A].dbo.Episodes.EpID),
[A].dbo.Episodes.EpName,
[A].dbo.Episodes.EpSynopsis,
[A].dbo.Episodes.EpDate,
convert(char(5),dateadd(minute, datediff(mi,epstarttime, case when ependtime < epstarttime then dateadd(day, 1, ependtime ) else ependtime end), 0),114) as duration,
[A].dbo.Episodes.EpPostedDate,
[A].dbo.Video.VideoTitle,
[A].dbo.Video.MediaItemID,
convert (varchar, [A].dbo.Video.VideoPostedDate, 101) as VideoPostedDate,
[A].dbo.Episodes.ShowID_FK,
[A].dbo.PhotoBank.PhotoFilepathFull AS MainImage,
PhotoBank_3.PhotoFilepathFull AS ThumbnailImage,
ROW_NUMBER() OVER (ORDER BY [A].dbo.Episodes.EpID DESC) AS RowNumber
FROM
[A].dbo.Episodes
INNER JOIN
[A].dbo.MasterShow ON [A].dbo.MasterShow.MasterID=510 LEFT OUTER JOIN
[A].dbo.PhotoBank ON [A].dbo.MasterShow.MasterMainImage_PhotoIdRef = [A].dbo.PhotoBank.PhotoID LEFT OUTER JOIN
[A].dbo.PhotoBank AS PhotoBank_1 ON [A].dbo.MasterShow.MasterFooterImage_PhotoIdRef = PhotoBank_1.PhotoID
INNER JOIN
[A].dbo.Video
ON
[A].dbo.Video.videoid =
(SELECT
min([A].dbo.Video.videoid)
FROM
[A].dbo.Video
WHERE
[A].dbo.Video.epid_ref =[A].dbo.Episodes.epid)
LEFT OUTER JOIN [A].dbo.PhotoBank AS PhotoBank_3 ON [A].dbo.Video.VideoThumb_PhotoIDRef = PhotoBank_3.PhotoID
WHERE
([A].dbo.Episodes.ShowID_FK =630 and [A].dbo.Episodes.EpDate < getdate())
ORDER BY [A].dbo.Episodes.EpDate Desc
FETCH NEXT FROM c2 INTO @showid
END
CLOSE c2
DEALLOCATE c2
)
SELECTRowNumber,
EpID,
EpName,
EpSynopsis,
EpDate,
duration,
EpPostedDate,
VideoTitle,
MediaItemID,
VideoPostedDate,
ShowID_FK,
MainImage,
ThumbnailImage
FROMMembers
WHERERowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC
[/code]
When i run the sql, Im getting this error,
[quote-0]Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near ')'.[/quote-0]
Can someone help me out here.
June 21, 2009 at 9:29 pm
It is not legal to DECLARE a cursor inside a common table expression (the WITH part). That is the cause of the specific error.
In fact the cursor is never used - it's not referenced by the query - so I am assuming that your posted code is lacking more than just the table declarations and sample data that we would normally expect.
Paul
June 22, 2009 at 12:56 am
Curious why you are using a cursor in your code. As this is posted in a SQL Server 2005 forum, I thought I'd post a simplified version of some paging code. This is only a single table, but it illistrates the point. I am using the AdventureWorks database.
declare @PageSize int,
@PageNum int;
set @PageSize = 20;
--------------------------------------------------------------------------------
set @PageNum = 1;
with PagedSalesOrders (
RowNumber,
AccountNumber,
SalesOrderID,
OrderDate
) as (
select
row_number() over (order by AccountNumber asc, OrderDate asc),
AccountNumber,
SalesOrderID,
OrderDate
from
Sales.SalesOrderHeader
)
select
*
from
PagedSalesOrders
where
RowNumber > @PageSize * (@PageNum - 1) and
RowNumber @PageSize * (@PageNum - 1) and
RowNumber @PageSize * (@PageNum - 1) and
RowNumber <= @PageSize * @PageNum
;
--------------------------------------------------------------------------------
June 22, 2009 at 12:59 am
If you post your table DDL, sample data (in a readily consummable format), and expected results for the paging code based on the sample data, I'm sure you will get back some really good, tested code.
Please read the first article I reference below in my signature block if you have any questions regarding teh requested information.
June 22, 2009 at 3:17 am
Hey Lynn,
I think that is the same technique that 'meg.81' linked to in the opening post.
You are right about the importance of sample data and expected output of course.
Paul
June 22, 2009 at 6:39 am
Paul,
Unless you are talking about the link to sqlteam (which I didn't follow), the opening code in both code blocks are both using a cursor and while loop. The code in the second code block attempts to declare a cursor in the CTE, which is the source of the error.
It shouldn't be too difficult to take the code I posted and modify it to the OP's requirements, we just need to know what those really are, and have the DDL and sample data to work with to accomplish it.
I posted some working code to show that there was no need for either the cursor or while loop.
June 22, 2009 at 2:52 pm
Lynn Pettis (6/22/2009)
Unless you are talking about the link to sqlteam (which I didn't follow)
That was it, yes. 🙂
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply