1 if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Paging_RowCount] ' ) and OBJECTPROPERTY (id, N ' IsProcedure ' ) = 1 ) 2 drop procedure [ dbo ] . [ Paging_RowCount ] 3 GO 4 5 SET QUOTED_IDENTIFIER ON 6 GO 7 SET ANSI_NULLS ON 8 GO 9 -- ------------------------------------------------------------- 10 -- 分页存储过程(使用RowCount) --edit by SiBen 11 -- summary: 12 -- 获取表或表集合的分页数据 13 -- 当多表连接时,sort列必须指定表名 14 -- ------------------------------------------------------------- 15 16 CREATE PROCEDURE Paging_RowCount 17 ( 18 @Tables varchar ( 1000 ), 19 @PK varchar ( 100 ), 20 @Sort varchar ( 200 ) = NULL , 21 @PageNumber int = 1 , 22 @PageSize int = 10 , 23 @Fields varchar ( 1000 ) = ' * ' , 24 @Filter varchar ( 1000 ) = NULL , 25 @Group varchar ( 1000 ) = NULL , 26 @RecordCount int = 0 output 27 ) 28 AS 29 30 /**/ /**/ /**/ /*Default Sorting*/ 31 IF @Sort IS NULL OR @Sort = '' 32 SET @Sort = @PK 33 34 /**/ /**/ /**/ /*Find the @PK type*/ 35 DECLARE @SortTable varchar ( 100 ) 36 DECLARE @SortName varchar ( 100 ) 37 DECLARE @strSortColumn varchar ( 200 ) 38 DECLARE @operator char ( 2 ) 39 DECLARE @type varchar ( 100 ) 40 DECLARE @prec int 41 42 /**/ /**/ /**/ /*Set sorting variables.*/ 43 IF CHARINDEX ( ' DESC ' , @Sort ) > 0 44 BEGIN 45 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' ) 46 SET @operator = ' < ' 47 END 48 ELSE 49 BEGIN 50 IF CHARINDEX ( ' ASC ' , @Sort ) > 0 51 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' ) 52 ELSE 53 SET @strSortColumn = @Sort 54 55 SET @operator = ' > ' 56 END 57 58 59 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0 60 BEGIN 61 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn )) 62 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn )) 63 END 64 ELSE 65 BEGIN 66 SET @SortTable = @Tables 67 SET @SortName = @strSortColumn 68 END 69 70 SELECT @type = t.name, @prec = c.prec 71 FROM sysobjects o 72 JOIN syscolumns c on o.id = c.id 73 JOIN systypes t on c.xusertype = t.xusertype 74 WHERE o.name = @SortTable AND c.name = @SortName 75 76 IF CHARINDEX ( ' char ' , @type ) > 0 77 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) ' 78 79 DECLARE @strPageSize varchar ( 50 ) 80 DECLARE @strStartRow varchar ( 50 ) 81 DECLARE @strFilter varchar ( 1000 ) 82 DECLARE @strSimpleFilter varchar ( 1000 ) 83 DECLARE @strGroup varchar ( 1000 ) 84 85 /**/ /**/ /**/ /*Default Page Number*/ 86 IF @PageNumber < 1 87 SET @PageNumber = 1 88 89 /**/ /**/ /**/ /*Set paging variables.*/ 90 SET @strPageSize = CAST ( @PageSize AS varchar ( 50 )) 91 SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 )) 92 93 /**/ /**/ /**/ /*Set filter & group variables.*/ 94 IF @Filter IS NOT NULL AND @Filter != '' 95 BEGIN 96 SET @strFilter = ' WHERE ' + @Filter + ' ' 97 SET @strSimpleFilter = ' AND ' + @Filter + ' ' 98 END 99 ELSE 100 BEGIN 101 SET @strSimpleFilter = '' 102 SET @strFilter = '' 103 END 104 IF @Group IS NOT NULL AND @Group != '' 105 SET @strGroup = ' GROUP BY ' + @Group + ' ' 106 ELSE 107 SET @strGroup = '' 108 109 /**/ /**/ /**/ /*Get rows count.*/ 110 DECLARE @str_Count_SQL nvarchar ( 500 )111 SET @str_Count_SQL = ' SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter 112 EXEC sp_executesql @str_Count_SQL ,N ' @TotalCount int=0 output ' , @RecordCount output 113 114 /**/ /**/ /**/ /*Execute dynamic query*/ 115 IF @Sort = @PK 116 BEGIN 117 EXEC ( 118 ' 119 DECLARE @SortColumn ' + @type + ' 120 SET ROWCOUNT ' + @strStartRow + ' 121 SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' 122 SET ROWCOUNT ' + @strPageSize + ' 123 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' = @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' 124 ' 125 ) 126 END 127 ELSE 128 BEGIN 129 EXEC ( 130 ' 131 DECLARE @SortColumn ' + @type + ' 132 DECLARE @SortNullValue ' + @type + ' 133 DECLARE @PKStartValue int134 SET @SortNullValue=CAST( '''' as ' + @type + ' )135 SET ROWCOUNT ' + @strStartRow + ' 136 SELECT @SortColumn= isNull( ' + @strSortColumn + ' ,@SortNullValue), @PKStartValue = ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc 137 SET ROWCOUNT ' + @strPageSize + ' 138 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull( ' + @strSortColumn + ' ,@SortNullValue) ' + @operator + ' @SortColumn or (isNull( ' + @strSortColumn + ' ,@SortNullValue)=@SortColumn and ' + @PK + ' <=@PKStartValue)) ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc139 ' 140 ) 141 END 142 GO 143 SET QUOTED_IDENTIFIER OFF 144 GO 145 SET ANSI_NULLS ON 146 GO