有点意思的分页存储过程

CREATE procedure main_table_pwqzc(@pagesize int,@pageindex int,@docount bit,@this_id)asif(@docount=1)beginselect count(id) from luntan where this_id=@this_idendelsebegindeclare @indextable table(id int identity(1,1),nid int)declare @PageLowerBound intdeclare @PageUpperBound intset @PageLowerBound=(@pageindex-1)*@pagesizeset @PageUpperBound=@PageLowerBound+@pagesizeset rowcount @PageUpperBoundinsert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time descselect a.* from luntan a,@indextable t where a.id=t.nidand t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.idendGO

?CREATE PROCEDURE Paging_RowCount(@Tables varchar(1000),@PK varchar(100),@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('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') 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 = @SortNameIF 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

?

转载自互联网