存储过程分页

在网上看了不少存储过程实现分页的资料，以下是我汇总前辈的经验，并结合自己的思考写出的存储过程：

ALTER procedure [dbo].[proc_page](@Columns nvarchar(4000)='*', --要获取的列@TableName nvarchar(500), --表名@Condition nvarchar(4000), --查询条件@OrderBy nvarchar(1000)='', --排序条件@PageNum int=1,--页码@PageSize int=20,--每页记录数@ID nvarchar(100),--主键ID@PageCount int output,--总页数@RecordCount bigint output --总记录数)asdeclare @Sql nvarchar(2000)--创建临时表，表中包括两项：根据给定条件获取的指定的数据表的主键id，行号rowindexcreate table #temptable(rowindex int identity(1,1) not null,id int)if @OrderBy<>'' begin exec('insert into #temptable(id) select '+@ID+' from '+@TableName+' where '+@Condition+' order by '+@OrderBy) endelse begin exec('insert into #temptable(id) select '+@ID+' from '+@TableName+' where '+@Condition) end--获取总记录数set @Sql='select @CountOut=count(*) from '+@TableName+' where '+@ConditionEXEC sp_executesql @Sql,N'@CountOut INT OUTPUT',@CountOut = @RecordCount OUTPUTset @PageCount=@RecordCount/@PageSizeif(@RecordCount%@PageSize>0)beginset @PageCount=@PageCount+1endif @PageNum<1beginset @PageNum=1;endif @PageNum>@PageCountbeginset @PageNum=@PageCountenddeclare @BRowNum int,@ERowNum int;--起始行号set @BRowNum=(@PageNum-1)*@PageSize--结束行号set @ERowNum=@BRowNum+@PageSizeset @Sql='select '+@Columns+' from ' + @TableName + ' A(nolock),#temptable T where '+@ID+'=T.id and '+@Condition+' and T.rowindex>'+Convert(varchar(10),@BRowNum)+' and T.rowindex<='+Convert(varchar(10),@ERowNum)if @OrderBy<>''beginset @Sql=@Sql+' order by '+@OrderByendExec(@Sql)

 

asp.net(C#)使用存储过程并得到需要的结果：

public bool PageInfo(out int pageCount, out Int64 recordCount) { bool res = false; pageCount = 1; recordCount = 0; SqlParameter[] parms = new SqlParameter[]{ new SqlParameter("@Columns",SqlDbType.NVarChar,4000), new SqlParameter("@TableName",SqlDbType.NVarChar,500), new SqlParameter("@Condition",SqlDbType.NVarChar,4000), new SqlParameter("@OrderBy",SqlDbType.NVarChar,1000), new SqlParameter("@PageNum",SqlDbType.Int,4), new SqlParameter("@PageSize",SqlDbType.Int,4), new SqlParameter("@ID",SqlDbType.NVarChar,100), new SqlParameter("@PageCount",SqlDbType.Int,4), new SqlParameter("@RecordCount",SqlDbType.BigInt,8) }; parms[0].Value = this.Columns; parms[1].Value = this.TableName; parms[2].Value = this.Condition; if (this.OrderBy != null) { parms[3].Value = this.OrderBy; } parms[4].Value = this.PageNum; parms[5].Value = this.PageSize; parms[6].Value = this.ID; parms[7].Direction = ParameterDirection.Output; parms[8].Direction = ParameterDirection.Output; using (SqlConnection conn = Infcon.create()) { SqlCommand cmd = new SqlCommand("proc_page", conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); foreach (SqlParameter parm in parms) { cmd.Parameters.Add(parm); } SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(this.Ds); //获取数据集 res = true; pageCount = (int)parms[7].Value == 0 ? 1 : (int)parms[7].Value;//获取存储过程输出值 recordCount = (Int64)parms[8].Value;//获取存储过程输出值 conn.Close(); } return res; }