• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

best practice for a sql script for pagination  RSS feed

 
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)
AS
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
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM notification_cursor
set @count = @count + 1

END
CLOSE notification_cursor
DEALLOCATE notification_cursor
--total number of records
select count(*) from notification where username = @username
GO
 
If I'd had more time, I would have written a shorter letter. -T.S. Eliot such a short, tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!