Sql Service中的分页

创建存储过程如下：

 1 CREATE PROCEDURE [dbo].[sp_GetPageList]
 2     @TableName varchar(5000),        --表名
 3     @SelectFields varchar(5000) = '*',    --字段名(全部字段为*)
 4     @OrderField varchar(5000),        --排序字段(必须!支持多字段)
 5     @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
 6     @pageSize int,                    --每页多少条记录
 7     @pageIndex int = 1 ,            --指定当前为第几页
 8     @totalRecord int = 0 OUTPUT,
 9     @TotalPage int output            --返回总页数
10 AS
11 BEGIN
12     Begin Tran --开始事务
13     Declare @sql nvarchar(4000); 
14     print @totalRecord
15     --if @totalRecord<=0 begin
16         --计算总记录数
17         if (@SqlWhere='' or @sqlWhere is NULL)
18             set @sql = 'select @totalRecord = count(*) from ' + @TableName
19         else
20             set @sql = 'select @totalRecord = count(*) from ' + @TableName + '  where ' + @sqlWhere
21             
22         EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       
23     --end
24     --计算总页数
25     select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
26     if (@SqlWhere='' or @sqlWhere is NULL)
27         set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
28     else
29         set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere    
30     --处理页数超出范围情况
31     if @PageIndex<=0 
32         Set @pageIndex = 1
33     if @pageIndex>@TotalPage
34         Set @pageIndex = @TotalPage
35      --处理开始点和结束点
36     Declare @StartRecord int
37     Declare @EndRecord int
38     set @StartRecord = (@pageIndex-1)*@PageSize + 1
39     set @EndRecord = @StartRecord + @pageSize - 1
40     --继续合成sql语句
41     set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
42         print @sql 
43     Exec(@Sql)
44     ---------------------------------------------------
45     If @@Error <> 0
46     Begin
47         RollBack Tran
48         Return -1
49     End
50     Else
51     Begin
52         Commit Tran
53         Return @totalRecord ---返回记录总数
54     End   
55 END

 

posted @ 2018-07-06 14:52  A&amp;B  阅读(501)  评论(0)  编辑  收藏  举报