存储过程分页

1CREATE PROCEDURE dbo.CreateSimple 2(

3 @PageIndex int, 4 @PageSize int 5) 6AS 7BEGIN

8 --定义三个变量:

9 -- @PageLowerBound :所取出记录的下限. 10 -- @PageUpperBound: 所要取出记录的上限.

11 -- @TotalRecords: 返回记录总数,主要用于页面的计算. 12 DECLARE @PageLowerBound int 13 DECLARE @PageUpperBound int 14 DECLARE @TotalRecords int 15

16 --计算上下限的值.

17 SET @PageLowerBound=@PageIndex * @PageSize 18 SET @PageUpperBound=@PageLowerBound+@PageSize-1 19

20--创建临时表:

21--IndexId是标识,自动增长1; 22--SimpleId由数据表[Simple]填充; 23 CREATE TABLE #PageIndexForSimple 24 (

25 IndexId int identity(1,1) NOT NULL, 26 SimpleId int 27 )

28--填充临时表

29 INSERT INTO #PageIndexForSimple(SimpleId) 30 SELECT s.[SimpleId] 31 FROM [Simple] s

32 --这里可以加WHERE condition和ODER BY语句 33

34 --取得记录总数,其实影响行数就是记录总数 35 SELECT @TotalRecords=@@ROWCOUNT 36

37 --获取我们所要的记录. 38 SELECT s.*

39 FROM [Simple] s,#PageIndexForSimple p 40 WHERE s.[SimpleId]=p.[SimpleId]

41 AND p.[IndexId]>=@PageLowerBound 42 AND P.[IndexId]<=@PageUpperBound 43 ORDER BY s.[Simple] 44

45 --返回记录总数.

46 RETURE @TotalRecords 47END

由上面的注释就能看懂了,呵呵,既然写到这里也把程序的代码写出来:

1 2 3 4 5 6 7 8 9101112131415161718

Public List GetSimple(int pageIndex,int pageIndex,out int totalRecords){ List entity=new List(); SqlParameter[]param=new SqlParameter[]{

new SqlParameter(\ new SqlParameter(\

new SqlParameter(\ };

param[0].Value=pageIndex; param[1].Value=pageSize;

param[2].Direction = ParameterDirection.ReturnValue;

SqlDataReader reader=SqlHelper.ExecuteReader(CommandType.StoredProcedure, \GetSimple\ While(reader.Read()){

entity.Add(GetSimpleEntity(reader)) }

reader.Close(); try{

totalRecords=(int)param[2].Value; }catch{}

19 return entity; 20}

ALTER PROCEDURE [dbo].[PROCE_SQL2005PAGECHANGE] (

@TableName varchar(50), --表名

@ReFieldsStr varchar(200) = '*', --字段名(全部字段为*)

@OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N'', --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) AS

BEGIN

--处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int;

Declare @TotalCountSql nvarchar(500);

Declare @SqlString nvarchar(2000);

set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1

SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句 --

IF (@WhereString! = '' or @WhereString!=null)

BEGIN

SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString; SET @SqlString =@SqlString+ ' where '+ @WhereString; END

--第一次执行得到 --IF(@TotalRecord is null) -- BEGIN

EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句

set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Exec(@SqlString) END