Error on Server Side Paging using SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error on Server Side Paging using SQL Server 2005

Author

Topic

megala
Starting Member

23 Posts

Posted - 2009-06-21 : 22:11:03

Hi,

Im referring to this sql method [url]http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005[/url]

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

when I add in to the paging sql, I did as


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
				)
		SELECT	RowNumber, 
			EpID, 
			EpName, 
			EpSynopsis, 
			EpDate, 
			duration,
			EpPostedDate,
			VideoTitle,
			MediaItemID,
			VideoPostedDate,
			ShowID_FK,
			MainImage,
			ThumbnailImage
		FROM	Members
		WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
		ORDER BY RowNumber ASC

When i run the sql, Im getting this error,

quote:
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 ')'.

Can someone pls help me out here.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 22:14:59

why are you using cursor ? the article did not mention anything about cursor.

Ditch the cursor and just put the select query into the CTE.

KH
[spoiler]Time is always against us[/spoiler]

megala
Starting Member

23 Posts

Posted - 2009-06-21 : 22:33:42

i need to use cursor to get the ID and pass it to the set @var = @showid to be used at the next sql.

megala
Starting Member

23 Posts

Posted - 2009-06-21 : 23:01:50

Hi khtan, since you mention it's because of the cursor, let me try modify my code without using the cursor first, see it works. I will update if it works.

Thank You.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 23:03:14

Don't need cursor looping.


SELECT	
	EpID,
	EpName,
	EpSynopsis,
	EpDate,
	duration,
	EpPostedDate,
	VideoTitle,
	MediaItemID,
	VideoPostedDate,
	showid,
	MainImage,
	ThumbnailImage,
	RowNumber
FROM
(
	SELECT 	
		A.dbo.Episdes.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(10), A.dbo.Video.VideoPostedDate, 101) AS VideoPostedDate,
		A.dbo.TVShow.showid,
		A.dbo.PhotoBank.PhotoFilepathFull AS MainImage,
		PhotoBank_3.PhotoFilepathFull AS ThumbnailImage,
		ROW_NUMBER() OVER (PARTITION BY A.dbo.TVShow.showid ORDER BY A.dbo.Episodes.EpID DESC) AS RowNumber
	FROM
		A.dbo.TVShow
	INNER JOIN
		A.dbo.Episodes 			ON A.dbo.TVShow.showid	= A.dbo.Episodes.ShowID_FK
	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.EpDate	< GETDATE()
) s
WHERE	
	RowNumber	<= 10
ORDER BY 
	EpDate DESC

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 23:05:43

you can also refer to here http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
on how to utilize row_number()

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources