Code
Create PROCEDURE [dbo].[usp_PagingLarge] ( @TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名 @PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空 @Fields VARCHAR(200) = '*', --要取出的字段，可以是多个表的字段，可以为空，为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页，0表示第1页 @RecNums INT output, --记录个数 @PageNums INT output, -- 页数 @Filter VARCHAR(200) = '', --条件，可以为空，不用填 where @Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by @Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by )AS/* 调用示例： declare @intRecNums int,@intPageNums int EXEC usp_PagingLarge 'TOrders left join TOrder_details on TOrders.order_id = TOrder_details.order_id', 'TOrders.order_id', '*', 5, 0, @intRecNums output, @intPageNums output, 'TOrders.supplier_id = 30', '', 'TOrders.order_id asc' print '记录个数 = ' + convert(char,@intRecNums) + ' ' + '页数 = ' + convert(char,@intPageNums)*/SET NOCOUNT ONBEGIN DECLARE @SortColumn VARCHAR(200) DECLARE @Operator CHAR(2) DECLARE @SortTable VARCHAR(200) DECLARE @SortName VARCHAR(200) declare @sql nvarchar(4000) declare @sqlTemp nvarchar(4000) IF @Fields = '' SET @Fields = '*' IF @Filter = '' SET @Filter = 'Where 1=1' ELSE SET @Filter = 'Where 1=1' + @Filter IF @Group <>'' SET @Group = 'GROUP BY ' + @Group IF @Order <> '' BEGIN DECLARE @pos1 INT, @pos2 INT SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC') IF CHARINDEX(' DESC', @Order) > 0 IF CHARINDEX(' ASC', @Order) > 0 BEGIN IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) SET @Operator = '<=' ELSE SET @Operator = '>=' END ELSE SET @Operator = '<=' ELSE SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') SET @pos1 = CHARINDEX(',', @SortColumn) IF @pos1 > 0 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) SET @pos2 = CHARINDEX('.', @SortColumn) IF @pos2 > 0 BEGIN SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) ELSE SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) END ELSE BEGIN SET @SortTable = @TableNames SET @SortName = @SortColumn END END ELSE BEGIN SET @SortColumn = @PrimaryKey SET @SortTable = @TableNames SET @SortName = @SortColumn SET @Order = @SortColumn SET @Operator = '>=' END DECLARE @type varchar(50) DECLARE @prec int Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype Where o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 --print @TopRows --print @Operator-- 统计记录个数 set @sqlTemp = 'Select @counts=count(*) FROM ' + @TableNames + ' ' + @Filter----取得查询结果总数量-----exec sp_executesql @sqlTemp,N'@counts int out',@RecNums out--取得分页总数if @RecNums <= @pageSize set @PageNums = 1else set @PageNums = (@RecNums / @pageSize) + 1select @sql = 'DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + convert(char,@TopRows) + ' Select @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order + ' SET ROWCOUNT ' + convert(char,@PageSize) + ' Select ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + ' ' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order--print @sql ------返回查询结果-----exec sp_executesql @sqlEND
转载于:https://www.cnblogs.com/lfzwenzhu/archive/2009/01/16/1376882.html
相关资源：数据结构—成绩单生成器
