谁有通用的存储过程分页程序？

yangydp 2003-07-22 04:41:43

谁有通用的存储过程分页程序？

...全文

27 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

zjcxc 2003-10-28

打赏
举报

参考:
http://expert.csdn.net/Expert/topic/2365/2365596.xml?temp=7.745624E-04

perfwell 2003-10-28

打赏
举报

ALTER 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 :表要排序列的列名，只能按一列排序,而且该列必须得在输出列表中；
-- @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

Outdll 2003-10-28

打赏
举报

http://www.asp.net/Forums/Download/Default.aspx?tabindex=0&tabid=1

下载这个ASP.NET Forums,然后看看他写的存储过程分页嘛!

--------------------------------------------------------------------
微软用的是临时表

yangydp 2003-07-30

打赏
举报

能否写出一个调用此存储过程的例子？谢谢！

zjcxc 2003-07-22

打赏
举报

/*
用存储过程实现的分页程序
*/
CREATE procedure Department_pagination
@SelectStr nvarchar(1000),
@ColumnStr nvarchar (1000),
@OrderStr nvarchar (1000),
@CurrentPage int,
@PageCount int
as
declare @TimeName nvarchar(25)
declare @TableStr nvarchar(1000)

select @TimeName = convert(nvarchar(23), getdate(), 121)
set @TimeName = REPLACE(@TimeName, '.', '')
set @TimeName = REPLACE(@TimeName, ':', '')
set @TimeName = REPLACE(@TimeName, '-', '')
set @TimeName = REPLACE(@TimeName, ' ', '')

select @TableStr='create table ##Tab' + @TimeName + '(wb int identity,'
exec(@TableStr+@ColumnStr+')')
exec('insert into ##Tab' + @TimeName + ' ' + @SelectStr + ' order by ' + @OrderStr)
exec('select * from ##Tab' + @TimeName + ' where wb between ((' + @CurrentPage + '-1)*' + @PageCount + '+1) and ' + @CurrentPage + '*' + @PageCount + ' order by wb')
exec('drop table ##Tab' + @TimeName)
GO

参数1：select语句。2：字段列表。3：排序字段。4：当前页。5每页数目

SpyX 2003-07-22