这是经常用的一个分页存储过程 希望大家指点不足
代码如下:
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 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)