SQL 存储过程分页

 1 USE [MOOSOFT_MASTER]
 2 GO
 3 /****** 对象:  StoredProcedure [dbo].[ViewPage]    脚本日期: 03/24/2010 14:00:11 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 ALTER Procedure [dbo].[ViewPage] 
10 @TableName varchar(50),        --表名
11 @Fields varchar(5000) = '*',    --字段名(全部字段为*)
12 @OrderField varchar(5000),        --排序字段(必须!支持多字段)
13 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
14 @pageSize int,                    --每页多少条记录
15 @pageIndex int = 1 ,            --指定当前为第几页
16 @TotalPage int output,            --返回总页数 
17 @totalRecord int output
18 AS
19 begin
20     Begin Tran --开始事务
21 
22     Declare @sql nvarchar(4000);
23     --Declare @totalRecord int output;    
24 
25     --计算总记录数
26          
27     if (@SqlWhere='' or @sqlWhere=NULL)
28         set @sql = 'SELECT @totalRecord = count(*) FROM ' + @TableName
29     else
30         set @sql = 'SELECT @totalRecord = count(*) FROM ' + @TableName + ' WHERE ' + @sqlWhere
31 
32     EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数        
33     
34     --计算总页数
35     select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
36 
37     if (@SqlWhere='' or @sqlWhere=NULL)
38         set @sql = 'SELECT ' + @Fields + ' FROM (SELECT ROW_NUMBER() Over(ORDER BY' + @OrderField + ') AS rowId,' + @Fields + ' FROM ' + @TableName 
39     else
40         set @sql = 'SELECT ' + @Fields + ' FROM (SELECT ROW_NUMBER() Over(ORDER BY ' + @OrderField + ') AS rowId,' + @Fields + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere    
41         
42     
43     --处理页数超出范围情况
44     if @PageIndex<=0 
45         Set @pageIndex = 1
46     
47     if @pageIndex>@TotalPage
48         Set @pageIndex = @TotalPage
49 
50      --处理开始点和结束点
51     Declare @StartRecord int
52     Declare @EndRecord int
53     
54     set @StartRecord = (@pageIndex-1)*@PageSize + 1
55     set @EndRecord = @StartRecord + @pageSize - 1
56 
57     --继续合成sql语句
58     set @Sql = @Sql + ') AS ' + @TableName + ' WHERE rowId between ' + Convert(varchar(50),@StartRecord) + ' AND ' +  Convert(varchar(50),@EndRecord)
59     
60     Exec(@Sql)
61     ---------------------------------------------------
62     If @@Error <> 0
63       Begin
64         RollBack Tran
65         Return -1
66       End
67      Else
68       Begin
69         Commit Tran
70         Return @totalRecord ---返回记录总数
71       End    
72 end
73 
74