对统计的结果进行分页的存储过程

create  proc CreateTempTable
@sqlString varchar(500),--要执行的sql语句
@pageIndex int,--页索引
@pageSize int,--页码
@doCount int=0--1是获取记录总数
as
begin
 declare @RealSql nvarchar(600),@tableSql nvarchar(300),@selectSql nvarchar(300),@delSql nvarchar(300)
 declare @tableName nvarchar(30),@pageSql nvarchar(600)
 set @tableName=CONVERT(nvarchar(11),getdate(),120)+CONVERT(nvarchar(12),getdate(),114)
 set @tableName=replace(@tableName,' ','')
 set @tableName=replace(@tableName,':','')
 set @tableName=replace(@tableName,'-','')
 set @tableName='t'+@tableName
 --创建临时表
 set @tableSql='create table '+@tableName+'(col_id int identity(1,1) primary key,col_date varchar(50),col_count int)'
 --获取统计到的数据
 set @RealSql='insert into '+@tableName+'(col_date,col_count) '+@sqlString
 --判断是要获取记录总条数还是显示某一页
 if @doCount=1
 begin
  set @pageSql='select count(*) as col_count from '+@tableName
 end
 else
 begin
  if @pageIndex=1
  begin
   set @pageSql='select top '+ convert(varchar,@pageSize) +' col_date,col_count from '+@tableName+' order by col_id '
   --print convert(int,@pageSize)
  end
  else
  begin
   set @pageSql='select top '+convert(varchar,@pageSize)+ ' col_date,col_count from '+@tableName+' where col_id >'
   set @pageSql=@pageSql+'(select max(col_id) from (select top '+convert(varchar,((@pageIndex-1)*@pageSize))+' col_id from '+@tableName+' order by col_id  ) a)'
  end
 end
 set @delSql='drop table '+@tableName
 exec sp_executesql @tableSql
 exec sp_executesql @RealSql
 exec sp_executesql @pageSql
 exec sp_executesql @delSql
end

 

 

 

declare @str nvarchar(500)
set @str='select convert(varchar(10),col_datetime,120) as col_date,count(*) as col_count from app_worker_send_sms_log  where  col_city=''0029'' and convert(varchar(10),col_datetime,120)>=''2006-01-01'' and convert(varchar(10),col_datetime,120)<=''2008-01-01'' group by convert(varchar(10),col_datetime,120) order by col_date'
exec CreateTempTable @str,4,12,0