-- 最通用的分页存储过程 -- 获取指定页的数据 -- Powered By:CHENQP -- Modify Date:17:08 2006-9-15 CREATEPROCEDURE usp_Pagination @tblNamevarchar(255), -- 表名 @strGetFieldsvarchar(1000) ='*', -- 需要返回的列 @fldNamevarchar(255)='', -- 排序的字段名 @PageSizeint=10, -- 页尺寸 @PageIndexint=1, -- 页码 @doCountbit=0, -- 返回记录总数, 非 0 值则返回 @OrderTypebit=0, -- 设置排序类型, 非 0 值则降序 @strWherevarchar(1500) =''-- 查询条件 (注意: 不要加 where) AS declare@strSQLvarchar(5000) -- 主语句 declare@strTmpvarchar(110) -- 临时变量 declare@strOrdervarchar(400) -- 排序类型 if@doCount!=0 begin if@strWhere!='' set@strSQL='select count(*) as Total from ['+@tblName+'] where '+@strWhere else set@strSQL='select count(*) as Total from ['+@tblName+']' end --以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都 --是@doCount为0的情况 else begin if@OrderType!=0 begin set@strTmp='<(select min' set@strOrder=' order by ['+@fldName+'] desc' --如果@OrderType不是0,就执行降序,这句很重要! end else begin set@strTmp='>(select max' set@strOrder=' order by ['+@fldName+'] asc' end if@PageIndex=1 begin if@strWhere!='' set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from ['+@tblName+'] where '+@strWhere+''+@strOrder else set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from ['+@tblName+'] '+@strOrder --如果是第一页就执行以上代码,这样会加快执行速度 end else begin --以下代码赋予了@strSQL以真正执行的SQL代码 set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from ['+@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' ['+@fldName+'] from ['+@tblName+']'+@strOrder+') as tblTmp)'+@strOrder if@strWhere!='' set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from ['+@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' ['+@fldName+'] from ['+@tblName+'] where '+@strWhere+''+@strOrder+') as tblTmp) and '+@strWhere+''+@strOrder end end exec ( @strSQL) GO