varchar(255), -- 表名 @fldNamevarchar(255), -- 字段名 @PageSizeint=10, -- 页尺寸 @PageIndexint=1, -- 页码 @IsCountbit=0, -- 返回记录总数, 非 0 值则返回 @OrderTypebit=0, -- 设置排序类型, 非 0 值则降序 @strWherevarchar(5000) =''-- 查询条件 (注意: 不要加 where) AS /**//********************************** Author:xuty date:20070706 descript:解密exec sp_decrypt 'GetRecordFromPage' GetRecordFromPage得到 Date:2007-07-06 @strWhere:最多只能存3500字节,不然系统可能会出错 **********************************/ declare@strSQLvarchar(7999) -- 主语句 declare@strTmpvarchar(5000) -- 临时变量 declare@strOrdervarchar(400) -- 排序类型 declare@countsint declare@strPageNumnvarchar(4000) declare@PageNumint if@OrderType!=0 begin set@strTmp='<(select min' set@strOrder=' order by ['+@fldName+'] desc' end else begin set@strTmp='>(select max' set@strOrder=' order by ['+@fldName+'] asc' end set@strSQL='select top '+str(@PageSize) +' * 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) +' * from [' +@tblName+'] where ['+@fldName+']'+@strTmp+'([' +@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' [' +@fldName+'] from ['+@tblName+'] where '+@strWhere+'' +@strOrder+') as tblTmp) and '+@strWhere+''+@strOrder -- if@PageIndex=1 begin set@strTmp='' if@strWhere!='' set@strTmp=' where '+@strWhere set@strSQL='select top '+str(@PageSize) +' * from [' +@tblName+']'+@strTmp+''+@strOrder end if@IsCount!=0 set@strSQL='select count(*) as Total from ['+@tblName+']' if@strWhere!='' begin set@strPageNum='select @counts=count(*) from ['+@tblName+']'+' where '+@strWhere end else begin set@strPageNum='select @counts=count(*) from ['+@tblName+']' end exec sp_executesql @strPageNum,N'@Counts int out ',@Counts out if@Counts<=@pageSizeset@PageNum=1 else set@PageNum= (@Counts/@pageSize) +1 exec (@strSQL) select@PageNum GO
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步