MSSQL SQL Efficient Paging method code

MSSQL SQL Efficient Paging method code

Alter PROCEDURE Dbo.proc_listpage
(
@tblname nvarchar,----a connection to a table or tables to display
@fldname nvarchar = ' * ',----list of fields to display
@pagesize int = 1,----the number of records displayed per page
@page int = Ten,----to display the record for that page
@pagecount int = 1 OUTPUT,----Total number of pages after the query results are paginated
@counts int = 1 OUTPUT,----number of records queried
@fldsort nvarchar = null,----sort field list or condition
@sort bit = 0,----Sort method, 0 is ascending, 1 is descending (if a multiple-field arrangement sort refers to the last

The order in which a sort field is sorted (the last sort field is not ordered)--program parameters such as: ' Sorta

ASC,SORTB DESC,SORTC ')
@strcondition nvarchar (1000) = NULL,----query criteria, no where
@id nvarchar,----primary key for the primary table
@dist bit = 0----Whether to add a query field distinct default 0 do not add/1 add
)
As
SET NOCOUNT ON
declare @sqltmp nvarchar (1000)----store dynamically generated SQL statements
declare @strtmp nvarchar (1000)----A query that holds the total number of query results
declare @strid nvarchar (1000)----A query that holds the ID at the beginning or end of a query

declare @strsorttype nvarchar (a)----data collation a
declare @strfsorttype nvarchar (a)----data collation B

DECLARE @sqlselect nvarchar----SQL construction of queries containing distinct
DECLARE @sqlcounts nvarchar----SQL construction of total queries containing distinct

If @dist = 0
Begin
Set @sqlselect = ' SELECT '
Set @sqlcounts = ' count (*) '
End
Else
Begin
Set @sqlselect = ' SELECT distinct '
Set @sqlcounts = ' count (distinct ' + @id + ') '
End

If @sort =0
Begin
Set @strfsorttype = ' ASC '
Set @strsorttype = ' desc '
End
Else
Begin
Set @strfsorttype = ' desc '
Set @strsorttype = ' ASC '
End

--------Generate query Statements--------
--Here @strtmp statements to get the number of query results
If @strcondition is null or @strcondition = '--no display condition set
Begin
Set @sqltmp = @fldname + ' from ' + @tblname
Set @strtmp = @sqlselect + ' @counts = ' + @sqlcounts + ' from ' + @tblname
Set @strid = ' from ' + @tblname
End
Else
Begin
Set @sqltmp = + @fldname + ' from ' + @tblname + ' WHERE (1>0) ' +

@strcondition
Set @strtmp = @sqlselect + ' @counts = ' + @sqlcounts + ' from ' + @tblname + '

where (1>0) ' + @strcondition
Set @strid = ' from ' + @tblname + ' WHERE (1>0) ' + @strcondition
End

----The total number of results obtained from the query-----
EXEC sp_executesql @strtmp, n ' @counts int out ', @counts out
DECLARE @tmpcounts int
If @counts = 0
Set @tmpcounts = 1
Else
Set @tmpcounts = @counts

--Get Total paging
Set @pagecount = (@tmpcounts + @pagesize-1)/@pagesize

/**//** the current page is greater than the total number of pages to take the last page **/
If @page > @pagecount
Set @page = @pagecount

--/*-----Data Paging 2-------* *
DECLARE @pageindex INT--Total/page size
DECLARE @lastcount INT--Total% page size

Set @pageindex = @tmpcounts/@pagesize
Set @lastcount = @tmpcounts% @pagesize
If @lastcount > 0
Set @pageindex = @pageindex + 1
Else
Set @lastcount = @pagesize

--//*** Display Page Paging
If @strcondition is null or @strcondition = '--no display condition set
Begin
If @pageindex <2 or @page <= @pageindex/2 + @pageindex% 2--first half data

Processing
Begin
Set @strtmp = @sqlselect + ' top ' + cast (@pagesize as varchar (4)) + ' +

@fldname + ' from ' + @tblname
+ ' where ' + @id + ' not in (' + @sqlselect + ' top ' + cast (@pagesize * (@page-1)

As varchar + ' + @id + ' from ' + @tblname
+ ' ORDER BY ' + @fldsort + ' + @strfsorttype + ') '
+ ' ORDER BY ' + @fldsort + ' + @strfsorttype
End
Else
Begin
Set @page = @pageindex-@page +1--the latter half of data processing
If @page <= 1--last page data display
Set @strtmp = @sqlselect + ' * FROM (' + @sqlselect + ' top ' + cast (@lastcount as

varchar (4)) + ' + @fldname + ' from ' + @tblname
+ ' ORDER BY ' + @fldsort + ' + @strsorttype + '] as TEMPTB ' + ' ORDER BY ' +

@fldsort + ' + @strfsorttype
Else
Set @strtmp = @sqlselect + ' * FROM (' + @sqlselect + ' top ' + cast (@pagesize as

varchar (4)) + ' + @fldname + ' from ' + @tblname
+ ' where ' + @id + ' not in (' + @sqlselect + ' top ' + cast (@pagesize * (@page-2)

+ @lastcount as varchar) + ' + @id + ' from ' + @tblname
+ ' ORDER BY ' + @fldsort + ' + @strsorttype + ') '

+ ' ORDER BY ' + @fldsort + ' + @strsorttype + '] as TEMPTB ' + ' ORDER BY ' +

@fldsort + ' + @strfsorttype
End
End

Else--There are query criteria
Begin
If @pageindex <2 or @page <= @pageindex/2 + @pageindex% 2--first half data

Processing
Begin
Set @strtmp = @sqlselect + ' top ' + cast (@pagesize as varchar (4)) + ' +

@fldname + ' from ' + @tblname
+ ' where ' + @id + ' not in (' + @sqlselect + ' top ' + cast (@pagesize * (@page-1)

As varchar + ' + @id + ' from ' + @tblname
+ ' WHERE (1>0) ' + @strcondition + ' ORDER BY ' + @fldsort + ' +

@strfsorttype + ') '
+ ' + @strcondition + ' ORDER BY ' + @fldsort + ' + @strfsorttype
End
Else
Begin
Set @page = @pageindex-@page +1--the latter half of data processing
If @page <= 1--last page data display
Set @strtmp = @sqlselect + ' * FROM (' + @sqlselect + ' top ' + cast (@lastcount as

varchar (4)) + ' + @fldname + ' from ' + @tblname
+ ' WHERE (1>0) ' + @strcondition + ' ORDER BY ' + @fldsort + ' +

@strsorttype + ') as TEMPTB ' + ' ORDER BY ' + @fldsort + ' + @strfsorttype
Else
Set @strtmp = @sqlselect + ' * FROM (' + @sqlselect + ' top ' + cast (@pagesize as

varchar (4)) + ' + @fldname + ' from ' + @tblname
+ ' where ' + @id + ' not in (' + @sqlselect + ' top ' + cast (@pagesize * (@page-2)

+ @lastcount as varchar) + ' + @id + ' from ' + @tblname
+ ' WHERE (1>0) ' + @strcondition + ' ORDER BY ' + @fldsort + ' +

@strsorttype + ') '
+ @strcondition + ' ORDER BY ' + @fldsort + ' + @strsorttype + ') as

TEMPTB ' + ' ORDER BY ' + @fldsort + ' + @strfsorttype
End
End

------Return Query Results-----
EXEC sp_executesql @strtmp
--print @strtmp
SET NOCOUNT OFF