/*
Released Date:2007-5-30
ProcName:UP_GetRecordByPage
*/
--check UP_GetRecordByPage
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_GetRecordByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_GetRecordByPage]
GO
create proc [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 排序字段(支持多字段,建议建索引) 比如 id asc ,addtime desc
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
--@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@TotalPages int output ,--输出参数,返回总页数
--@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
BEGIN
declare @strSql nvarchar(4000)
declare @rowsCount int
if Ltrim(Rtrim(@strWhere))=''
SET @strWhere=' 1=1 '
--obtain RowsCount
SET @strSql='select @TotalRecords=COUNT(*) from '+@tblName+' where '+@strWhere
execute SP_executesql @strSql,N'@TotalRecords int output ',@rowsCount output
select @TotalPages=CEILING((@rowsCount+0.0)/@PageSize)
if @PageIndex<=0
SET @PageIndex=1
if @PageIndex > @TotalPages
SET @PageIndex=@TotalPages
SET @strSql='select * from (select top (@PageIndex*@PageSize) * ,ROW_NUMBER() OVER(order by '+@fldName+') as rowNumber from '+@tblName+' where '+@strWhere+' ) t where t.rowNumber>=((@PageIndex-1)*@PageSize+1)'
--print @strSql
exec sp_executesql @strSql,N'@PageIndex int, @PageSize int',@PageIndex,@PageSize
END
@fldName是排序字段不是selectList字段【*】,输入形式 id asc,addtime desc
去掉2个参数OrderType,IsReCount。OrderType在@fldName体现,IsReCount在原来的存储过程没有多大意义。添加了一个输出参数@TotalPages
strWhere 不用加where