分页排序存程过程
create PROCEDURE up_custompage
@vc_order_column_name_main varchar(100),
@vc_order_column_name varchar(100),
@vc_select_column_list varchar(100),
@vc_select_table_list varchar(100),
@vc_condition varchar(100),
@page_size int,
@current_page int,
@total1 int output
--@vc_order_column_name_main :表的主键,排序列的列名,而且该列必须得在输出列表中,;
-- @vc_order_column_name :表要排序列的列名,只能按一列排序,而且该列必须得在输出列表中;
--1和2不能为同一字段
-- @vc_select_column_list :返回列的列名列表,可以为多个列;
-- @vc_select_table_list:要查询的表名及关联表的连接;
--@vc_condition:查询条件的字符串,必须要有查询条列,否则会抛出异常;
--@page_size:每页显示记录的条数;
--@current_page:当前页的页码;
--@total1:所有符合条件的记录的总数。
AS
declare @temp1 varchar(500),@temp2 nvarchar(500),@page_total int,@last_page int
set @temp2=N'select @total2=count(*) from ' + @vc_select_table_list + ' where ' + @vc_condition
exec sp_executesql @temp2,N' @total2 int output ',@total1 output
if @total1>0
begin
set @page_total=(@total1+@page_size-1)/@page_size
if @current_page<@page_total or @total1%@page_size=0
set @temp1='select top ' + cast(@page_size as varchar(4)) + ' * from
(select top ' + cast(@page_size as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name_main
+ ' in (select top ' + cast(@page_size*@current_page as varchar(10)) + ' ' + @vc_order_column_name_main + ' from ' +
@vc_select_table_list + ' where '+ @vc_condition + ' order by ' +
@vc_order_column_name +','+@vc_order_column_name_main+ ') order by ' + @vc_order_column_name +' desc ,' + @vc_order_column_name_main+' desc) as temp1 order by ' + @vc_order_column_name +','+@vc_order_column_name_main
else
begin
set @last_page=@total1%@page_size
set @temp1='select top ' + cast(@last_page as varchar(4)) + ' * from
(select top ' + cast(@last_page as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name_main
+ ' in (select top ' + cast(@total1 as varchar(10)) + ' ' + @vc_order_column_name_main + ' from ' +
@vc_select_table_list + ' where '+ @vc_condition + ' order by ' +
@vc_order_column_name +','+@vc_order_column_name_main+ ') order by ' + @vc_order_column_name + ' desc ,'+@vc_order_column_name_main+ ' desc) as temp1 order by ' + @vc_order_column_name +','+@vc_order_column_name_main
end
print @temp1
exec(@temp1)
end
else
return