分页控件配合高效存储过程分页（1）

-------------------------------------------------
--                           **********
-- ***************分页存储过程***************
--        *****create in 2009.03.05*****
-------------------------------------------------
CREATE PROCEDURE spPagination
@table_name nvarchar(500), --表名
@content_info nvarchar(500), --要查询的列名
@sql_where nvarchar(500), --查询条件
@id_name nvarchar(200), --标识列
@order_by nvarchar(200), --排列字段
@order_type int, --排序类型，1为顺序0为倒序
@page_row int, --每页显示多少行记录
@index_page int, --当前页码
@row_count int output, --总行数
@page_num int output --总页数
as
begin
declare @str_sql nvarchar(500)
declare @top_num_1 int
declare @top_num_2 int
declare @top_value_1 nvarchar(20)
declare @top_value_2 nvarchar(20)
declare @str_sql_where nvarchar(500)
declare @is_desc nvarchar(20)
declare @str_where_rows nvarchar(500)

if @sql_where != ''
begin
     set @str_sql_where = 'and '+ @sql_where
     set @str_where_rows = ' where '+ @sql_where
end
else
begin
     set @str_sql_where = ''
     set @str_where_rows = ''
end
if @order_type = 1
begin
     set @is_desc = ''
end
else
begin
     set @is_desc = 'desc'
end

create table #result(row_num varchar(50))
declare @str_sql_count nvarchar(500)
set @str_sql_count = 'select count(0) from '+ @table_name +' '+ @str_where_rows   ----查询有多少行记录
insert #result exec(@str_sql_count)
select @row_count = row_num from #result
drop table #result

set @top_num_2 = (@index_page - 1) * @page_row
set @top_value_1 = @page_row
set @top_value_2 = @top_num_2

if @row_count % @page_row = 0
begin
     set @page_num = @row_count / @page_row
     set @str_sql = 'select top '+ @top_value_1 +' '+ @content_info +' from '+@table_name+' where '+ @id_name
         +' not in (select top '+ @top_value_2 +' '+ @id_name +' from '+@table_name+') '+ @str_sql_where +' order by '+
         @order_by +' '+ @is_desc
end
else
begin
     declare @dt_row int
     set @dt_row = @row_count % @page_row
     set @page_num = (@row_count - @dt_row) / @page_row + 1
     if @index_page < @page_num
     begin
          set @str_sql = 'select top '+ @top_value_1 +' '+ @content_info +' from '+@table_name+' where '+ @id_name
              +' not in (select top '+ @top_value_2 +' '+ @id_name +' from '+@table_name+') '+ @str_sql_where +' order by '+
              @order_by +' '+ @is_desc
     end
     else
     begin
          set @str_sql = 'select '+ @content_info +' from '+@table_name+' where '+ @id_name +' not in (select top '+
              @top_value_2 +' '+ @id_name +' from '+@table_name+') '+ @str_sql_where +' order by '+ @order_by +' '+ @is_desc
     end
end
--print @row_count
--print @page_num
exec(@str_sql)
end

 

test :

declare @row_count int ,@page_num int
declare @str_row nvarchar(20) ,@str_page nvarchar(20)
exec spPagination 'T_WEB_OTC_Cert_Quota a inner join T_WEB_OTC_Cert_MOM b on a.Number = b.Number',
'a.Number,a.PA,a.CertificateNo,a.OnlineRegNo,b.NoOfSupported,b.PaUsed,b.NewPAtype','','a.Number','a.Number',
1,10,3,@row_count out,@page_num out
set @str_row = @row_count
set @str_page = @page_num
print '一共有 '+ @str_row +' 行'
print '一共有 '+ @str_page +' 页'

 

转载于:https://www.cnblogs.com/Cien/archive/2009/03/16/1413279.html