Create Procedure Paging_Rowcount (@tables varchar (1000), @ sort varchar (200) = null, @ Pagenumber INT = 1, @ PageSize Int = 10, @ 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) / * Default Page Number * / IF @PageNumber <1 SET @PageNumber = 1
/ * 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
