清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

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 @totalpage=@recordcount/@pageSize
				else
					set @totalpage=@recordcount/@pageSize+1
			if @totalpage <=1
			 set @currentPage=1 
			 if @totalpage <@currentPage 
			  set @currentPage=@totalpage
		  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