MS SQL 数据分页(通用)

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

CREATE PROCEDURE [dbo].[sp_GetListByPageAndFileds]
( 
	@pageSize  int,/**  每页数据量        **/   
	@currentPage int = 1,/**  当前页，默认为为1 **/       
	@fields   varchar(2000),/**  查询字段，可以用 * 表示所有 **/     
	@tablename  varchar(max),/**  表名，或者为查询得出的 子表 ，子表查询sql需要括号括起来，并指定新表名  **/     
	@orderString varchar(1000),/**  排序字段 + asc/desc   **/       
	@whereString varchar(1000)  /**  不包含‘where’的字符串 **/      
)
	AS
	BEGIN
		 DECLARE @sql varchar(2000)
		 DECLARE @strOrder varchar(2000)
		 DECLARE @strWhere varchar(2000)
		 declare @recordcount int 
		 declare @convertorderstr varchar(2000)
		declare @countsql nvarchar(4000)
		declare @totalpage int
		 set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','')
		 if @strOrder != ''
		  set @strOrder = ' order by ' + @strOrder
		 else
		  set @strOrder = ' order by ID DESC'
		set @strOrder=lower(@strOrder)
		set @convertorderstr=replace(@strOrder,'desc','d_e_s_c')
		set @convertorderstr=replace(@convertorderstr,'asc','desc')
		set @convertorderstr=replace(@convertorderstr,'d_e_s_c','asc')
		 set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','')
		 if @strWhere != ''
		  set @strWhere = ' where ' + @strWhere

		set @countsql='select @a=count(*) from ' + @tablename + @strWhere
		exec  sp_executesql @countsql,N'@a int output',@recordcount output 
		 if @pageSize = 0
		  set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder
		 else
		 begin
				if @recordcount%@pageSize=0
					set @[email?protected]/@pageSize
				else
					set @[email?protected]/@pageSize+1
			if @totalpage <=1
			 set @currentPage=1 
			 if @totalpage <@currentPage 
			  set @[email?protected]
		  if @currentPage = 1
		   set @sql = 'select top ' + Str(@pageSize)+' '+ @fields + ' from ' + @tablename + @strWhere + @strOrder
		  else
			if (@currentPage - 1) * @pageSize > @recordcount / 2
				set @sql = 'select top ' + str(@pageSize) + ' * from (select top ' + str((@recordcount - (@currentPage - 1) * @pageSize)) + ' ' + @fields + ' from ' + @tablename + @strWhere + @convertorderstr + ') as t1  ' + @strOrder
			else
				set @sql = 'select * from(select top ' + str(@pageSize) + ' * from (select top ' + str(@pageSize * @currentPage) + ' ' + @fields + ' from ' + @tablename + @strWhere + @strOrder + ') as t1  ' + @convertorderstr + ') as t2  ' + @strOrder
			end
			set @sql = @sql + '; select '+str(@recordcount)+' as cnt'
		 exec(@sql)
	END

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!