Full SQL paging stored procedures (multi-table joins supported)

Http://www.cnblogs.com/andiki/archive/2009/03/24/1420289.html

Code
/*********************************************************
* Role: Data paging (full SQL paging stored procedure (multi-table joins supported))
* Author: Zi Liping
* Date Created: 2009-03-23
* Instructions for use:
1. Single-table call method: EXEC proc_datapagination @tblName = ' IPA ', @fldName = ' * ', @pageSize =50, @page = 6789, @fldSort = ', @Sort = 1, @strC Ondition = ', @ID = ' ID ', @Dist = 0, @pageCount = null, @Counts = NULL
2, multi-table call method: EXEC proc_datapagination @tblName = ' Info left JOIN infotype it on It.typeid=info.tid ', @fldName = ' Info.*,it.tnam E ', @pageSize =50, @page = 1806, @fldSort = ", @Sort = 0, @strCondition =", @ID = ' ID ', @Dist = 0, @pageCount = null, @Counts = Null
* Multi-table joint query use need to NOTE: 1, the primary key field in multiple tables cannot be the same name. 2, multiple tables can not allow fields with the same name, if there is a field with the same name you can use as rename
*********************************************************/
Alter PROCEDURE [dbo]. [Proc_datapagination]
(
@tblName nvarchar,----the connection to the table or tables to display
@fldName nvarchar = ' * ',----list of fields to display
@pageSize int = Ten,----the number of records displayed per page
@page int = 1,----to display the record for that page
@fldSort nvarchar = null,----sort field list or condition
@Sort bit = 0,----Sort method, 1 is ascending, 0 is descending (if multi-field arrangement sort refers to the order of the last sort field (the last sort field is not sorted)-Program passes such as: ' Sorta ASC,SORTB desc,sortc ')
@strCondition nvarchar (+) = NULL,----query condition, where is not required
@ID nvarchar,----primary key of the primary table
@Dist bit = 0,----whether to add a query field DISTINCT default 0 does not add/1 add
@pageCount int = 1 OUTPUT,----the total number of pages after the query results are paged
@Counts int = 1 output----The number of records queried
)
As
SET NOCOUNT on
Declare @sqlTmp nvarchar (----) to store dynamically generated SQL statements
Declare @strTmp nvarchar (----) A query statement that holds the total number of query results
Declare @strID nvarchar (----) A query statement that holds the ID of the beginning or end of the query

Declare @strSortType nvarchar ()----data collation a
Declare @strFSortType nvarchar ()----data Collation B

Declare @SqlSelect nvarchar----SQL constructs for queries that contain distinct
Declare @SqlCounts nvarchar (----) SQL constructs the total number of queries that contain distinct


If @Dist = 0
Begin
Set @SqlSelect = ' SELECT '
Set @SqlCounts = ' Count (0) '
End
Else
Begin
Set @SqlSelect = ' SELECT distinct '
Set @SqlCounts = ' Count (DISTINCT ' [email protected]+ ') '
End


If @Sort =0
Begin
Set @strFSortType = ' DESC '
Set @strSortType = ' DESC '
End
Else
Begin
Set @strFSortType = ' ASC '
Set @strSortType = ' ASC '
End

If (@fldSort is not null and @fldSort <> ")
Begin
Set @fldSort = ', ' [email protected]
End
Else
Begin
Set @fldSort = ' '
End

--------Generate query Statements--------
--@strtmp here to get the number of query result statements
If @strCondition is null or @strCondition = "--no display condition is set
Begin
Set @sqlTmp = @fldName + ' from ' + @tblName
Set @strTmp = @SqlSelect + ' @Counts = ' [email protected]+ ' from ' [email protected]
Set @strID = ' from ' + @tblName
End
Else
Begin
Set @sqlTmp = + @fldName + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
Set @strTmp = @SqlSelect + ' @Counts = ' [email protected]+ ' from ' [email protected] + ' where (1>0) ' + @strCondition
Set @strID = ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
End

----Get the total number of query results-----
EXEC sp_executesql @strTmp, N ' @Counts int out ', @Counts out
DECLARE @tmpCounts int
If @Counts = 0
Set @tmpCounts = 1
Else
Set @tmpCounts = @Counts

--Get the total number of pages
Set @pageCount = (@[email protected])/@pageSize

/**//** the current page is greater than the total number of pages to take the last page **/
If @page > @pageCount
Set @[email protected]

--/*-----Data page 2-minute processing-------*/
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

--to match the display
--set Nocount OFF
--select @page curpage, @pageSize pageSize, @pageCount countpage, @tmpCounts [Rowcount]
--set Nocount ON

--//*** display pagination
If @strCondition is null or @strCondition = "--no display condition is set
Begin
If @pageIndex <2 or @page <[email protected]/2 + @pageIndex% 2--data processing in the first half
Begin
If @page =1
Set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR) + ' + @fldName + ' from ' [email protected]
+ ' ORDER BY ' + @ID + ' + @[email protected]
Else
Begin
Set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR) + ' + @fldName + ' from ' [email protected]
+ ' where ' [email protected]
If @Sort =0
Set @strTmp = @strTmp + ' > (select Max ('
Else
Set @strTmp = @strTmp + ' < (select min ('
Set @strTmp = @strTmp + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + "+ @ID + ' from ' [ Email protected]
+ ' ORDER BY ' + @ID + ' + @[email protected]+ ') as Tbminid) '
+ ' ORDER BY ' + @ID + ' + @[email protected]
End
End
Else

Begin
Set @page = @[email protected]+1--data processing in the latter part
If @page <= 1--last page data display
Set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@lastcount as VARCHAR) + "+ @fldName + ' from ' [email Protected
+ ' ORDER BY ' + @ID + "+ @[email protected]+ ') as TEMPTB ' + ' ORDER BY ' + @ID + ' + @[email protected]
Else
Begin
Set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@pageSize as VARCHAR) + "+ @fldName + ' from ' [email p Rotected]
+ ' where ' [email protected]
If @Sort =0
Set @[email protected]+ ' < (select min ('
Else
Set @[email protected]+ ' > (select Max ('
Set @[email protected]+ @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) [email protected] as Varchar (20)) + ' + @ID + ' from ' [email protected]
+ ' ORDER BY ' + @ID + ' + @[email protected]+ ') as Tbmaxid) '
+ ' ORDER BY ' + @ID + "+ @[email protected]+ ') as TEMPTB ' + ' ORDER BY ' + @ID + ' + @[email protected]
End
End

End

Else--there are query conditions
Begin
If @pageIndex <2 or @page <[email protected]/2 + @pageIndex% 2--data processing in the first half
Begin
If @page =1
Set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR) + ' + @fldName + ' from ' [email protected]
+ ' where 1=1 ' + @strCondition + ' ORDER BY ' + @ID + ' + @[email protected]
Else
Begin
Set @[email protected]+ ' top ' + CAST (@pageSize as VARCHAR) + ' + @fldName + ' from ' [email protected]
+ ' where ' [email protected]
If @Sort =0
Set @strTmp = @strTmp + ' > (select Max ('
Else
Set @strTmp = @strTmp + ' < (select min ('

Set @strTmp = @strTmp + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + "+ @ID + ' from ' [ Email protected]
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @ID + ' + @[email protected]+ ') as Tbminid) '
+ ' + @strCondition + ' ORDER BY ' + @ID + ' + @[email protected]
End
End
Else
Begin
Set @page = @[email protected]+1--data processing in the latter part
If @page <= 1--last page data display
Set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@lastcount as VARCHAR) + "+ @fldName + ' from ' [email Protected
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @ID + "+ @[email protected]+ ') as TEMPTB ' + ' ORDER BY ' + @ID + ' + @[email p Rotected]
Else
Begin
Set @[email protected]+ ' * FROM (' [email protected]+ ' top ' + CAST (@pageSize as VARCHAR) + "+ @fldName + ' from ' [email p Rotected]
+ ' where ' [email protected]
If @Sort =0
Set @strTmp = @strTmp + ' < (select min ('
Else
Set @strTmp = @strTmp + ' > (select Max ('
Set @strTmp = @strTmp + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) [email protected] as Varchar (20)) + ' + @ID + ' from ' [email protected]
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @ID + ' + @[email protected]+ ') as Tbmaxid) '
+ ' + @strCondition + ' ORDER BY ' + @ID + "+ @[email protected]+ ') as TEMPTB ' + ' ORDER BY ' + @ID + ' + @[email protected]
End
End

End

------Returns the results of the query-----
SET NOCOUNT off
EXEC sp_executesql @strTmp
Print @strTmp

Full SQL paging stored procedures (multi-table joins supported)