SP that support data Paging

Aryo Handono

Hello all, I want to create some store procedure that retieve data from a table, that storeprcedure should take 2 parameters PageNumber and RowInAPage so if I use "SomeStoreProcedure 1,100", it will display data from record 1 - 100. And If I use "SomeStoreProcedure 3,100", it will display data from record 301 - 400. any idea ? "Courage choose who will follow, Fate choose who will lead" - Lord Gunner, Septerra Core "Press any key to continue, where's the ANY key ?" - Homer Simpsons Drinking gives me amazing powers of insight. I can solve all the worlds problems when drunk, but can never remember the solutions in the morning. - Michael P Butler to Paul Watson on 12/08/03

michanne

You have a couple of choices, Take your pick :) http://www.sqlservercentral.com/columnists/glarsen/sequentialnumbering\_printversion.asp Barbara

basementman

a simplified example:

if exists (select * from sysobjects where type = 'U' and name = 'SearchResults')
exec ('drop table SearchResults')
GO
create table SearchResults
(
SearchSeqNo int NOT NULL IDENTITY,
UserKey int NOT NULL,
SearchType tinyint NOT NULL,
-- 1 = Settlement Online Approval (SOA)
-- 2 = Dispatch Tower Activity (DSP)
-- 3 = Assigment Activity (AL)
-- 4 = Salvage Checkin (CI)
-- 5 = Salvage Image (IMG)
-- 6 = Salvage Provider (SPL)
KeyValue int NOT NULL,
Dummy int NULL
)
GO
create unique clustered index SearchResults_idx
on SearchResults (UserKey, SearchType, SearchSeqNo)
GO

if exists (select * from sysobjects where type = 'P' and name = 'ProviderOpenSearch')
drop procedure ProviderOpenSearch
GO
create procedure ProviderOpenSearch @iUserKey int,
@cCriteria varchar(8000),
@iEntriesPerPage int = 0,
@bReturnResults int = 0,
@iSortColumn int = 0
AS

declare
@cUserKey varchar(10),
@cSortBy varchar(7000),
@cSortDirection varchar(20),
@iRowsFound int,
@iPageCount int

set nocount on

delete from SearchResults where UserKey = @iUserKey and SearchType = 6

if IsNull(@iEntriesPerPage,0) = 0
select @iEntriesPerPage = 20

if IsNull(@cCriteria,'') = ''
select @cCriteria = ' 1 = 1'

select @cSortDirection = '', @cSortBy = 'SP.Salvage_Provider_ID', @iSortColumn = IsNull(@iSortColumn,0)

if @iSortColumn > 100
select @cSortDirection = 'DESC', @iSortColumn = @iSortColumn - 100

if @iSortColumn = 1
select @cSortBy = 'SP.Salvage_Provider_ID'
else if @iSortColumn = 2
select @cSortBy = 'SP.Salvage_Provider_Name'
else if @iSortColumn = 3
select @cSortBy = 'SP.Address_Line_1'
else if @iSortColumn = 4
select @cSortBy = 'SP.City_Name'
else if @iSortColumn = 5
select @cSortBy = 'SP.State_Abbreviation'
else if @iSortColumn = 6
select @cSortBy = 'S.Status_Description'
else if @iSortColumn = 7
select @cSortBy = 'SPG.Salvage_Provider_Group_Name'
else if @iSortColumn = 8
select @cSortBy = 'OT.Office_Type_Description'

select @cUserKey = Convert(varchar(10),@iUserKey)

exec ('insert SearchResults
(
KeyValue,
UserKey,
SearchType
)
select
Salvage_Provider_ID,
' + @cUserKey + ',
6
from
Salvage_Provider