MSSQL 存储过程分页 测试 1

没有进行仔细的测试 只是对算法进行了点研究
参考了别人的存储过程分页的算法 再加上自己想的

declare @TotalRecord real
declare @Page_Size real
declare @Page_Count real
declare @Page_Order int
declare @SQLQuery varchar(1000)

SELECT @TotalRecord=COUNT(Digitaltion_ID) FROM [DigitalART_Digitaltion]

Set @Page_Order = 2
Set @Page_Size = 10
Set @Page_Count = CEILING(@TotalRecord/@Page_Size)
SELECT @Page_Count AS Page_Count, @Page_Size AS Page_Size, @Page_Order AS Page_Order

IF (@Page_Order > @Page_Count/2)
  BEGIN
    Set @SQLQuery = 'SELECT TOP '+Cast(@Page_Size AS varchar(30))+' Digitaltion_ID FROM [DigitalART_Digitaltion] WHERE Digitaltion_ID IN (SELECT TOP '+CAST(@TotalRecord - (@Page_Order - 1)*@Page_Size AS varchar(30))+' Digitaltion_ID FROM [DigitalART_Digitaltion] ORDER BY Digitaltion_ID Asc) ORDER BY Digitaltion_ID Desc'
  END
Else
  BEGIN
    Set @SQLQuery = 'SELECT TOP '+Cast(@Page_Size AS varchar(30))+' Digitaltion_ID FROM [DigitalART_Digitaltion] WHERE Digitaltion_ID NOT IN (SELECT TOP '+CAST((@Page_Order-1)*@Page_Size AS varchar(30))+' Digitaltion_ID FROM [DigitalART_Digitaltion] ORDER BY Digitaltion_ID Desc) ORDER BY Digitaltion_ID Desc'
  END
SELECT @SQLQuery
EXEC(@SQLQuery)
GO