分页的存储过程
支持分页和排序功能,传入SQL语句、页索引、页容量即可实现分页的存储过程,急用!
--------------------编程问答--------------------
网上搜一下吧,这个存储过程一大把,
--------------------编程问答--------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[sp_jinqi_new_article_dal]
@sqlWhere varchar(5000) = '', --条件语句(不用加where)
@pageSize int =10, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@totalRecord int = 0 output --返回记录总数
AS
/*
用于分页查询记录
*/
BEGIN
SET NOCOUNT ON;
Declare @sql nvarchar(4000);
Declare @totalPage int;
--计算总记录数
set @sql = 'select @totalRecord = count(*) from Table1 t
inner join Table2 s on (t.column_id=s.column_id and t.language_id=s.language_id) where t.flag=1 ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
select @totalPage=CEILING((@totalRecord+0.0)/@pageSize)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by t.author_time desc) as rowId,
t.*,s.column_type,s.column_name_english,s.column_save_path,case when DateDiff (day,getdate(),dateadd(day,s.page_new_days,t.author_time)) >0 then 1 else 0 end is_new
from Table1 t
inner join Table2 s on (t.column_id=s.column_id and t.language_id=s.language_id)
where t.flag=1 ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@totalPage
Set @pageIndex = @totalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@pageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
print @sql
Exec(@Sql)
END
--------------------编程问答--------------------
我也想要,嘿嘿
--------------------编程问答--------------------
create proc selectPage(
@index int,--页索引
@size int --页容量
)
as
begin
declare @startindex int
declare @endindex int
if(@index=1)
set @startindex=@index*(@size-1)
else
set @startindex=@index*(@size-1)+1
set @endindex=@index*@size
select * from
(select row_number() over (order by '字段') as Num,* from tablename) AS temp
where Num between @startindex and @endindex
end
--------------------编程问答--------------------
--------------------编程问答--------------------
http://www.cnblogs.com/alanliu/archive/2008/01/04/1025318.html
补充:.NET技术 , ASP.NET