Win a copy of The Way of the Web Tester: A Beginner's Guide to Automating Tests this week in the Testing forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

best practice for a sql script for pagination

john mattucci
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi im not sure if this is the appropriate forum for my question, but here goes. I am trying to include pagination in my jsp. I will allow the user to choose how many items to be diplayed per page. I will then query the database for each batch of information. I was wondering if my you felt that my sql script(MSSQL2000) is the most appropriate for my task.
CREATE PROCEDURE dbo.notificationProcedure @start int, @end int, @username varchar(255)
DECLARE notification_cursor SCROLL CURSOR FOR
SELECT * FROM notification
WHERE username = @username
OPEN notification_cursor
-- Perform the first fetch.
FETCH ABSOLUTE @start FROM notification_cursor
DECLARE @count int
SET @count = @start
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0 AND @count <= @end
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM notification_cursor
set @count = @count + 1

CLOSE notification_cursor
DEALLOCATE notification_cursor
--total number of records
select count(*) from notification where username = @username
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic