Microsoft SQL Server 2005 存储过程翻页

Create Procedure [dbo].[Sys_ListPages]
(
@Page int=1,/*传进页数*/
@Page_Size int=20,/*每页行数*/
@Page_Count int=0,/*页面总数*/
@Row_Count int=0,/*统计行数*/
@Get_Type nvarchar(10),/*返回类型*/
@SQL_Count nvarchar(2000),/*查询语句*/
@SQL_String nvarchar(3000),/*查询语句*/
@SQL_Order nvarchar(1000)/*排序字段*/
)
AS

 

Set NoCount ON--如果返回记录集，用这句话是个好习惯

 

/*判断页数*/
IF(@Page='' or @Page<1)
 Begin
  Set @Page=1
 End

/*判断行数*/
IF(@Page_Size='' or @Page_Size<1)
 Begin
  Set @Page_Size=20
 End

/*判断是否Count(*)*/
IF(@Page=1 or @Row_Count='' or @Row_Count<1)
 Begin
  Set @SQL_Count=N'Select @Row_Count='+@SQL_Count
  Execute sp_executesql @SQL_Count,N'@Row_Count Int Output',@Row_Count output
 End

/*页面总数*/
 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
   Set @List_ID2=(@Page_Size*@Page)--当前页记录结束
   Set @List_ID1=(@List_ID2-@Page_Size)--当前页记录开始
 
  Set @SQL_String=N'Select * From (Select ROW_NUMBER() Over('
      +@SQL_Order
      +N')AS List_ID,'
      +@SQL_String
      +N')AS A Where A.List_ID>'
      +Cast(@List_ID1 AS Nvarchar)
      +N' and A.List_ID<='
      +Cast(@List_ID2 AS Nvarchar)
  Execute sp_executesql @SQL_String
  Return
 End