sql2005存储过程分页的一个优秀的例子
USE [jcwork]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[query_tbliuyan]
(
@Page int=1,
@Page_Size int=20,
@Row_Count int=-1,
@area nvarchar(20)=N'',
@keyword nvarchar(100)=N'',
@affirm int=1,
@Page_count int=100,
@Get_Type nvarchar(400)=N'Get_Data'
)
AS
Set NoCount ON--如果返回记录集,用这句话是个好习惯
declare @strquery as nvarchar(300)
declare @SQL_String as nvarchar(400)
set @strquery=N'where 1=1'
IF(@area<>'')
Begin
set @strquery=@strquery+N' and fdarea='''+@area+''' '
End
--print @strquery
IF(@keyword<>'')
Begin
set @strquery=@strquery+N' and (fdTitle like ''%'+@keyword+'%'' or fdComments like ''%'+@keyword+'%'' or fdCorpname like ''%'+@keyword+'%'')'
End
IF(@affirm<>-1)
Begin
set @strquery=@strquery+N' and fdaffirm='+cast(@affirm as nvarchar)
End
--print @strquery
IF(@Page='' or @Page<1)
Begin
Set @Page=1
End
IF(@Page_Size='' or @Page_Size<1)
Begin
Set @Page_Size=20
End
declare @SQL_Count as nvarchar(300)
IF(@Row_Count='' or @Row_Count<1)
Begin
Set @SQL_Count=N'Select @Row_Count='+N' (select count(*) from tbliuyan '+@strquery+')'
print @SQL_Count
Execute sp_executesql @SQL_Count,N'@Row_Count Int Output',@Row_Count output
End
--print @Row_count
IF(@Row_Count%@Page_Size>0)
Begin
Set @Page_Count = (@Row_Count/@Page_Size)+1
End
Else
Begin
Set @Page_Count = @Row_Count/@Page_Size
End
IF(@Page<1)
Begin
Set @Page=1
End
IF(@Page>@Page_Count)
Begin
Set @Page=@Page_Count
End
IF(@Get_Type='Get_Count')--获取统计信息
Begin
Select @Page as Page,@Page_Count AS Page_Count,@Row_Count AS Row_Count
Return
End
IF(@Get_Type='Get_Data')--获取数据记录
Begin
Declare @List_ID1 int,@List_ID2 int
--print @page
Set @List_ID2=(@Page_Size*@Page)--当前页记录结束
Set @List_ID1=(@List_ID2-@Page_Size)--当前页记录开始
Set @SQL_String=N'Select * From (Select ROW_NUMBER() Over(order by fdcreatedate desc)AS List_ID,'
+N'fdliuyanID,fdtitle,fdcorpname,fdarea,convert(char(10),fdCreateDate,120) as CreateDate,fdreply FROM TBLIUYAN '+@strquery
+N')AS A Where A.List_ID>'
+Cast(@List_ID1 AS Nvarchar)
+N' and A.List_ID<='
+Cast(@List_ID2 AS Nvarchar)
--print @SQL_String
Execute sp_executesql @SQL_String
--print @SQL_String
--Execute sp_executesql N'Select * From (Select ROW_NUMBER() Over(fdprodid'
Return
End
----===================================
--
--首先进存储过程返回统计数据,算出页面。
--
--再进存储过程返回要查询的记录集。这里不用怀疑两次查询的速度,保证比通常的翻页性能提升很多。最麻烦的是Count时所耗费的时间,合理建立字段索引(不要乱建索引,要不数据库成倍增长性能反而猛降),应该还是快的,如果没有模糊查询,Count结果可以保存起来,库内自动更新(面向存储过程编码),用的时候就不用再执行直接调用,性能应该提升更多更多,再给数据表分区。