/*************************************************************** 
 分页存储过程  
 Create BY Tellov 2008-05-28 
 tellov@gmail.com 
 注意:使用本存储过程,SQL语句中必须要带有Order BY 子句 
  
 支持多表链接查询,支持别名,支持嵌套SELECT语句 
 注:语句中如果带有group by ,则页总数和记录数无法正确统计 
  
 -- Modify BY Tellov 2008-12-10 修正了多字段排序且序不同时分页会出现返回数据集不正确的问题 
 -- Modify By Tellov 2009-04-10 将@temp从varchar(4000)改为varchar(max),修正了SQL语句太长会出现超过4000之外的字符丢失的问题。 
 *************************************************************** 
 参数说明: 
 1.@sql			查询语句 
 2.@CurrentPage	当前页 
 3.@PageSize		页大小 
 4.@Counts		查询到的记录总数 
 5.@pageCount	页总数 
 ***************************************************************/ 
 ALTER PROCEDURE [dbo].[Sp1_Exec_Page] 
 ( 
 	@Sql nvarchar(4000),				-- 要执行的SQL语句 
 	@CurrentPage int = 1,			-- 当前页 
 	@PageSize int = 30,				-- 每页纪录数 
 	@counts int output,				-- 纪录总数 
 	@pageCount int output			-- 页总数 
 ) 
 --WITH ENCRYPTION 
 AS 
  
 Set Lock_TimeOut 2000 
  
 declare @sort nvarchar(500) 
 declare @variableName nvarchar(300) 
 declare @setVariableName nvarchar(2000) 
 declare @where nvarchar(4000) 
 declare @temp nvarchar(max) 
 declare @tempwhere nvarchar(2000) 
 select @sql = replace(@sql,'  ',' ') 
 select @where = '' 
 select @setVariableName = '' 
 select @variableName = '' 
  
  
 -- 取出orderBy 
 if CHARINDEX(' order by ',@sql) = 0 
 begin 
 	select '使用此存储过程时查询语句中必须带上order by 子句'; 
 end 
 else 
 begin 
 	declare @wherelocation int,@orderbylocation int,@groupbylocation int 
  
 	-- 取最后一个order by 的位置 
 	select @orderbylocation = CHARINDEX(' order by ',@sql) 
 	while CHARINDEX(' order by ',@sql,@orderbylocation+10)  <> 0 
 	begin 
 		select @orderbylocation = CHARINDEX(' order by ',@sql,@orderbylocation+10) 
 	end 
  
 	-- 默认当前页 
 	IF @CurrentPage  < 1 
 		SET @CurrentPage = 1 
  
 	-- 获取总行数 
 	select @temp = left(@Sql,@orderbylocation)  
 	select @temp = 'Select @counts = count(1) from ( ' + @temp + ') as __tt' 
 	--print @temp 
 	exec sp_executesql @temp,N'@counts int output',@counts output 
  
 	-- 获取总分页数 
 	If @counts  < @pageSize 
 		Select @pageCount = 1 
 	else 
 		Select @pageCount = ((@Counts-1) / @pageSize) +1 
  
 	-- 提高效率,如果是第一页直接返回 
 	if @CurrentPage = 1 
 	begin 
 		declare @returntemp nvarchar(4000) 
 		select @returntemp = 'select top ' + convert(nvarchar(20),@pageSize) + substring(@sql,CHARINDEX('select ',@sql) + 6,len(@sql)) 
 		exec(@returntemp) 
 		--print @returntemp 
 	end 
 	else 
 	begin		 
 		-- 取最后一个WHERE的位置 
 		select @wherelocation = CHARINDEX(' where ',@sql) 
 		while @wherelocation  <> 0 and CHARINDEX(' on ',@sql,@wherelocation + 7)  <> 0 
 		begin 
 			select @wherelocation = CHARINDEX(' where ',@sql,@wherelocation + 7) 
 		end 
  
 		-- 取最后一个group by 的位置 
 		select @groupbylocation = CHARINDEX(' group by ',@sql) 
 		while CHARINDEX(' group by ',@sql,@groupbylocation+10)  <> 0 
 		begin 
 			select @groupbylocation = CHARINDEX(' group by ',@sql,@groupbylocation+10) 
 		end 
  
 		select @sort = substring(@sql,@orderbylocation+10,len(@sql)) 
  
 		-- 生成执行SQL时必须的语句 
 		select @sort = @sort + ',' 
 		select @tempwhere =