| *************************************************************** |
| ** 千万数量级分页存储过程，配合EXTJS中的GridPanel分页 ** |
| *************************************************************** |
| |
| 参数说明: |
| 1.@tbName :表名称,视图，不带dbo. |
| 2.@pkFiled :主关键字 |
| 3.@sortExp :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc (Desc Asc前必须加空格) |
| 4.@start :每页起始行号，第一页是从0开始计算（ExtJs） |
| 5.@limit :每页显示记录数 |
| 6.@fields: 字段名 |
| 7.@filter :过滤语句，不带Where |
| 8.@group :Group语句,不带Group By |
| |
| 返回说明： |
| 返回记录集，table[0]分页后的记录，table[1]当前数据源总记录数 |
| |
| 测试语句： |
| Exec [spExtJsPager] @tbName = 'Roles', |
| @pkFiled = 'RoleID', |
| @start = 3, |
| @limit = 3 |
| |
| ***************************************************************/ |
| ALTER PROCEDURE [dbo].[spExtJsPager] |
| ( |
| @tbName varchar(1000), |
| @pkFiled varchar(100), |
| @sortExp varchar(200) = NULL, |
| @start int = 0, |
| @limit int = 20, |
| @fields varchar(1000) = '*', |
| @filter varchar(1000) = NULL, |
| @group varchar(1000) = NULL |
| ) |
| AS |
| BEGIN |
| SET NOCOUNT ON |
| |
| /*默认排序*/ |
| SET @sortExp = RTRIM(LTRIM(@sortExp)) |
| IF @sortExp IS NULL OR @sortExp = '' |
| SET @sortExp = @pkFiled |
| |
| DECLARE @sortTable varchar(100) |
| DECLARE @sortName varchar(100) |
| DECLARE @SQL varchar(4000) |
| |
| /*设定排序语句.*/ |
| DECLARE @strStartRow varchar(50),@strEndRow varchar(50) |
| DECLARE @strFilter varchar(1000) |
| DECLARE @strGroup varchar(1000) |
| |
| /*默认当前页*/ |
| IF @start < 0 |
| SET @start = 0 |
| |
| /*设置分页参数.*/ |
| SET @strStartRow = CAST((@start + 1) AS varchar(50)) |
| SET @strEndRow = CAST((@start + @limit) AS varchar(50)) |
| |
| /*筛选以及分组语句.*/ |
| IF @filter IS NOT NULL AND @filter != '' |
| BEGIN |
| SET @strFilter = ' Where ' + @filter + ' ' |
| END |
| ELSE |
| BEGIN |
| SET @strFilter = '' |
| END |
| |
| IF @group IS NOT NULL AND @group != '' |
| BEGIN |
| SET @strGroup = ' GROUP BY ' + @group + ' ' |
| END |
| ELSE |
| BEGIN |
| SET @strGroup = '' |
| END |
| |
| /*执行查询语句*/ |
| SET @SQL ='select * from ( |
| Select |
| ROW_NUMBER() OVER(Order By '+@sortExp+') as SysRowNum, |
| ' + @fields + ' |
| FROM ' + @tbName + @strFilter + @strGroup + ' |
| ) v |
| where SysRowNum between '+@strStartRow+' and '+@strEndRow |
| |
| SET @SQL = @SQL + 'select count('+@pkFiled+') from '+@tbName + @strFilter + @strGroup |
| EXEC (@SQL) |
| END |
| |
| set ANSI_NULLS ON |
| set QUOTED_IDENTIFIER ON |
| go |
| |
