简单存储过程分页

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

 

ALTER          proc pagesplit
@current_page int,
@sum_per_page int,
@db nvarchar(100),
@etc nvarchar(1000),
@key nvarchar(50),
@order varchar(10),
@ID varchar(10)

as

declare @sum int

declare @sql varchar(1000)

set @sum=@current_page*@sum_per_page

--print @etc
if @current_page>0

 if @order='desc'
 set @sql='select top '+STR(@sum_per_page)+' * from '+@db+' where ('+@ID+' < (select min('+@ID+') from (select top '+STR(@sum)+' '+@ID+' from '+@db+' where 1=1 '+@etc+' order by '+@ID+' desc) as t) '+@etc+') '+@etc+' order by '+@key+' '+@order
 else
 set @sql='select top '+STR(@sum_per_page)+' * from '+@db+' where ('+@ID+' > (select max('+@ID+') from (select top '+STR(@sum)+' '+@ID+' from '+@db+' where 1=1 '+@etc+' order by '+@key+') as t) '+@etc+') '+@etc+' order by '+@key+' '+@order
else

set @sql='select top '+STR(@sum_per_page)+' * from '+@db+' where 1=1 '+@etc+' order by '+@key+' '+@order

print @sql
exec(@sql)

 

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO