SQL 分页存储过程

--Author: Longxi Zhu--Date: 2016-06-02--Description: Get Paged listCreate procedure usp_Cosmos_GetPagedJobs@tableName varchar(50),@fields varchar(max),@strWhere varchar(max),--条件@orderField varchar(50),@pageIndex int=1,--页索引@pageSize int,--每页显示条数@pageCount int output,--总页数@totalCount int output--总条数 ASbeginset nocount on;declare @sql nvarchar(max);if(isnull(@strWhere,'')='')beginset @sql='select @totalCount=count(*)from ' +@tableName;endelsebeginset @sql='select @totalCount=count(*) from '+@tableName+' where '+@strWhereendexec sp_executesql @sql,N'@totalCount int output',@totalCount output;--计算总条数--计算总页数select @pageCount=CEILING((@totalCount+0.0)/@pageSize);if(isnull(@strWhere,'')='')beginset @sql='select * from (select row_number() over(order by '+@orderField+') as rowId,'+@fields+' from '+@tableName;endelsebeginset @sql='select * from (select row_number() over(order by '+@orderField+') as rowId,'+@fields+' from '+@tableName+' where '+@strWhere;end--处理页超出范围情况if(@pageIndex<=0)beginset @pageIndex=1;endif(@pageIndex>@pageCount)beginset @pageIndex=@pageCount;end--处理开始点和结束点declare @beginIndex int,@endIndex intset @beginIndex=(@pageIndex-1)*@pageSize+1set @endIndex=@pageIndex*@pageSize--继续组织sql语句set @sql=@sql+') as a'+' where rowId between '+Convert(varchar,@beginIndex) +' and '+Convert(varchar,@endIndex);exec (@sql);set nocount off;enddeclare @pageCount1 int, @totalCount1 intexec usp_Cosmos_GetPagedJobs 'jobs','*','alias like ''%v-lozhu%''','id',3,3,@pageCount1 output,@totalCount1 output