扩展性很好的一个分页存储过程分享

这是经常用的一个分页存储过程 希望大家指点不足

use [a6756475746]
go
/****** object: storedprocedure [dbo].[tbl_order_searchwhereandpage] script date: 11/01/2011 09:37:39 ******/
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure [dbo].[tbl_order_searchwhereandpage]
@allcount int output,
@pageindex int,
@pagesize int ,
@mindate datetime,
@maxdate datetime
as
begin
declare @pagelower int
set @pagelower=@pagesize * @pageindex
declare @pageupper int
set @pageupper= @pagelower + @pagesize - 1

declare @searchsql nvarchar(4000)
set @searchsql='select * ,( row_number() over (order by [id] desc) -1 ) as rownumber from tbl_order where (1=1) '
declare @searchsqlcount nvarchar(4000)
set @searchsqlcount='select @count=count(*) from tbl_order where (1=1) '
declare @result [varchar](5000)
set @result=''

if @mindate>convert(datetime,'1900-1-2')
begin
set @result=@result+' and odeliverydate >= '''+convert(varchar(20),@mindate)+''''
end
if @maxdate > convert(datetime,'1900-1-2')
begin
set @result=@result+' and odeliverydate <= '''+convert(varchar(20),dateadd(dd,1,@maxdate))+''''
end
set @searchsqlcount=@searchsqlcount+@result
set @searchsql=@searchsql+@result

set @searchsql = 'with t as (' + @searchsql +' )
select * from t
where [rownumber] between '+ convert(varchar(50),@pagelower) +' and '+ convert(varchar(50),@pageupper) + '
order by rownumber '

exec (@searchsql)

exec sp_executesql @searchsqlcount ,n'@count as int out' ,@allcount out

print @searchsql

print @allcount
end
exec (@result)