Paging with SP - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Paging with SP

Author

Topic

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-24 : 23:57:56

i have code below


IF @StatementType = 'Select'
	BEGIN		
		--SELECT @gettotalpage=COUNT(*) FROM Departemen 
		--SELECT @totalpage = @gettotalpage % 10
		--SELECT nmDepartemen FROM Departemen
		DECLARE @startRowIndex INT=1,@pageSize INT=10 
		SELECT *
		FROM
		(SELECT ROW_NUMBER() OVER (ORDER BY IdDepartemen) AS Row,nmDepartemen     
		FROM Departemen) AS dep
		WHERE
			(Row between (@startRowIndex) AND @startRowIndex + @pageSize - 1)

with the code above i have successful view data 10 record first until 10

My Question :
1. how to make next view coz i only success view 10 record not the next record
2. how to make back to the previous page..

for a while only that my question..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-25 : 02:15:03

[code]CREATE PROCEDURE dbo.usp_ShowPageNumber
(
@pageNumber INT = 1
)
AS

SET NOCOUNT ON;

SELECT nmDepartemen
FROM (
SELECT NTILE(10) OVER (ORDER BY IdDepartemen) AS [page],
nmDepartemen
FROM dbo.Departemen
) AS dep
WHERE [page] = @pageNumber;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-25 : 04:47:38

quote:

Originally posted by SwePeso

CREATE PROCEDURE dbo.usp_ShowPageNumber
(
	@pageNumber INT = 1
)
AS

SET NOCOUNT ON;

SELECT	nmDepartemen
FROM	(
		SELECT	NTILE(10) OVER (ORDER BY IdDepartemen) AS [page],
			nmDepartemen
		FROM	dbo.Departemen
	) AS dep
WHERE	[page] = @pageNumber;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

thank's for yr code but for see the next record for the example :
first view 1-10, second view 11-20,third view 21-30

coz in my program only see 1-10 record..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-25 : 04:58:54

did you call the sp with the needed parm (@pageNumber)?

Too old to Rock'n'Roll too young to die.

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-25 : 05:15:58

ok thanks for yr help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources