Create Procedure Pro_Paging (@tables varchar (100), @ pk varchar (100), @ sort varchar (200) = null, @ Pagenumber INT = 1, @ PageSize Int = 10, @ Totalcount Int Output, --Add by DCBOY @ Totalpage int outprut, --add by dcboy @ Fields varchar (1000) = '*', @filter varchar (1000) = null, @ group varchar (1000) = null) AS
/ * Default sorting * / if @sort is null or @sort = '' set @sort = @pk
/ * Find the @pk type * / declare @SortTable varchar (100) Declare @Sortname varchar (100) Declare @strsortcolumn varchar (200) Declare @operator char (2) declare @Type varchar (100) Declare @prec int
/ * Set sorting variables. * / 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 = '> =' end
IF 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 end
SELECT @ 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 (1000) Declare @strsimplefilter varchar (1000) Declare @strgroup varchar (1000)
------------------------------------ Declare @SQL nvarchar (1000) if @filter = '' Set @ SQL = 'SELECT @ Totalcount = Count (' @ pk ') from' @ TablesELSE SET @ SQL = 'SELECT @ Totalcount = Count (' @ pk ') from' @ Tables 'Where' @filterexec sp_executesql @ sql, N '@ TotalCount int out', @ TotalCount out - print @TotalCountIF @ TotalPage% @ PageSize = 0 SET @ TotalPage = @ TotalCount / @ PageSizeELSE SET @ TotalPage = @ TotalCount / @ PageSize 1
--Print @totalpage ------------------------------------
/ * Default page number * / if @PAGenumber <1 set @PAGENUMBER = 1IF @PAGENUMBER> @totalpage set @ Pagenumber = @ TotalPage
/ * SET PAGING VARIABLES. * / Set @strpagesize = CAST (@PageSize As Varchar (50)) set @strstartrow = cast ((@ Pagenumber - 1) * @ PageSize 1) As varchar (50))
/ * SET FILTER & GROUP VARIABLES. * / 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 = '/ * Execute dynamic query * / EXEC ( '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 ') Go
