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 2000 Forums  SQL Server Development (2000)  Universal stored procedure for paging

Author  Topic  fizgig Starting Member 34 Posts Posted - 2004-04-08 : 09:35:43 Hi, I use a great stored procedure for paging (see below). I use it a lot, but i have to customize it for every table or view that should be paged. I want to edit it so i only have one universal stored procedure for paging that can be called like this: sp_pager table_name, order_by_field, page_number, recs_per_page. That will save me a lot of time Does anyone has a good suggestion where to start? Bye, Ward ### CREATE PROCEDURE paged ( @Newsletter varchar(200), @RecsPerPage int, @Page int, @orderColumn varchar (250), @direction varchar (250) ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Create a temporary table CREATE TABLE ##TempPagedSubscriber ( [id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [realid] [numeric](18, 0) NULL, [name] [varchar](250) NULL, [email] [varchar](250) NULL, [subscribed] [bit] NULL, [since] [datetime] NULL, [organisation][varchar] (100) NULL, [department_function][varchar] (100) NULL, [telephone][varchar] (20) NULL, ) -- Insert the rows from tblItems into the temp. table Exec ('INSERT INTO ##TempPagedSubscriber (realid, name, email, subscribed, since, organisation,department_function,telephone) SELECT id, name, email, subscribed, since, organisation, department_function, telephone FROM subscriber WHERE newsletter = ' + @Newsletter + ' ORDER BY ' + @orderColumn + ' ' + @direction) -- Find out the first and last record we want DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) -- Now, return the set of paged records, plus, an indiciation of we -- have more records or not! SELECT *, MoreRecords = ( SELECT COUNT(*) FROM ##TempPagedSubscriber TI WHERE TI.ID >= @LastRec ) FROM ##TempPagedSubscriber WHERE ID > @FirstRec AND ID < @LastRec DROP TABLE ##TempPagedSubscriber -- Turn NOCOUNT back OFF SET NOCOUNT OFF GO