SQL SERVER分页算法
ALTER PROCEDURE [dbo].[PageedProcedure]
@tblName varchar(1000), -- 表名 @strGetFields varchar(1000), -- 需要返回的列 @pKey varchar(255), --主键,产生唯一id键 @orderName varchar(255)='', -- 排序的字段名 @orderType varchar(4)='ASC', -- ASC,DESC @PageIndex int = 1, -- 页码 @PageSize int = 10, -- 页尺寸 @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(5000) -- 主语句 if @strWhere != '' set @strSQL ='SELECT ' + @strGetFields + ', ROW_NUMBER() OVER (ORDER BY ' + @pkey + ' ' + @orderType + ') AS ROWNUMBER FROM ' + @tblName + ' WHERE ' + @strWhere else set @strSQL ='SELECT ' + @strGetFields + ', ROW_NUMBER() OVER (ORDER BY ' + @pkey + ' ' + @orderType + ') AS ROWNUMBER FROM ' + @tblName set @strSQL ='WITH ORDEREDORDERS AS (' + @strSQL + ') SELECT *,totalRow=(select DISTINCT count(*) from ORDEREDORDERS) FROM ORDEREDORDERS WHERE (ROWNUMBER BETWEEN ' + str(@PageIndex) + ' AND ' + str(@PageSize+@PageIndex-1) + ')' if @orderName !='' begin set @strSQL=@strSQL+' ORDER BY ' + @orderName +' '+ @orderType end --进行排序处理 exec (@strSQL) |
SQL SERVER分页算法
最新推荐文章于 2024-05-21 20:29:42 发布