Alter procedure sp_pagination / ************************************************************* ******************* Paging store procedure *********************************** *********************************************** Parameter Description: Tables: Table Name, View 2 .PrimaryKey: Main Keyword 3.sort: Sort statement, without order By, such as: newsid desc, ORDERROWS ASC4.CURRENTPAGE: CurrentPage: Current page 5.pageSize: Page Size 6.fields: Field 6.Filter: Filter Screwdrings without WHERE 7.Group: Group statement, no group by8.docount: 1 Return to the total number of lines, 0 return list ************************************ ******************************************* / (@ Tables VARCHAR (1000), @primaryKey Varchar (100) , @ Sort varchar (200) = null, @ currentpage int = 1, @ PageSize INT = 10, @ Fields varchar (1000) = '*', @filter varchar (1000) = null, @ group varchar (1000) = null , @ docount bit = 0) AS / * Default Sort * / if @Sort is null or @Sort = '' set @Sort = @PrimaryKeyDeclare @SortTable Varchar (100) Declare @Sortname Varchar (100) Declare @strsortcolumn varchar (200 ) Declare @operator char (2) declare @Type varchar (100) Declare @PREC INT
/ * Setting the sort statement. * / If charIndex ('dec', @ Sort)> 0 begin set @strsortcolumn = replace (@Sort, 'dec', '') set @operator = '<=' endelse Begin if Charindex ('ASC', @Sort) = 0 set @strsortcolumn = Replace (@sort, 'asc', '') set @operator = '> =' endif charindex ('.', @STRSORTCOLUMN)> 0 begin set @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ( '.', @ strSortColumn)) SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ( '.', @ strSortColumn) 1, LEN (@strSortColumn)) ENDELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn ENDSELECT @ type = t.name, @ prec = c.precFROM sysobjects o JOIN syscolumns c on o.id = c.idJOIN systypes t on c.xusertype = t.xusertypeWHERE o.name = @SortTable and c.name = @Sortname
If Charindex ('Char', @Type)> 0 set @Type = @Type '(' Cast (@PREC As Varchar) ')'
Declare @strpagesize varchar (50) Declare @strstartrow varchar (50) Declare @strfilter varchar (200) Declare @strsimplefilter varchar (200) Declare @strgroup varchar (200)
/ * Default current page * / if @currentpage <1 set @currentpage = 1
/ * Set the page parameters. * / Set @strpagesize = cast (@PageSize as varchar (50)) set @strstartrow = cast ((@ currentpage - 1) * @ PageSize 1) As varchar (50))
/ * Screening and packet statement. * / If @filter is not null and @filter! = '' Begin set @strfilter = 'where' @filter '' set @strsimplefilter = 'and' @filter 'endelse Begin set @strsimplefilter = '' set @strfilter = 'endif @Group is not null and @group! =' 'Set @strGroup =' Group by ' @Group ' 'Else set @strgroup =' '
Declare @cTemp nvarchar (1000) Decloy @PageCount Int, @LineCount Decimal
Create Table #TEMP (Linecount Int)
Set @cTemp = 'INSERT INTO #TEMP (LINECOUNT) SELECT Count (*) from' @tables @strfilter '' @strgroup Exec (@cTemp)
SELECT @LineCount = linecount from #Temp
DROP TABLE #TEMP
IF (@ docount = 1) Begin select @LineCount 'total number' endelse
Begin - Total Page SET @PageCount = CEiling (@ linecount / @ strpagesize) if @currentpage> @PageCount Begin Set @cTemp = 'SELECT' @fields 'from' @tables 'Where 1> 2' end else begin / * execute a query * / set @cTemp = 'DECLARE @SortColumn' @type 'SET ROWCOUNT' @strStartRow 'SELECT @ SortColumn =' @strSortColumn 'FROM' @Tables @strFilter '' @strGroup 'ORDER BY' @Sort 'SET ROWCOUNT' @strPageSize 'SELECT' @Fields 'FROM' @Tables 'WHERE' @strSortColumn @operator '@SortColumn ' @STRSIMPLEFILTER ' ' @StrGroup ' ORDER BY ' @Sort ' 'end - print @cTemp EXEC (@cTemp) end
-------------------------------------------------- ---
