MSSQL 存储过程分页 测试 4

这个是最 XML 版的 没进行仔细测试 可能还有纰漏

/*
  作者: SLIGHTBOY
  建立日期: 2004-10-18
  修改日期:
  存储作用: 作品浏览分页
  作用数据库: DigitalART_Digitaltion
  输入变量:
  输出变量:
*/
CREATE PROCEDURE dbo.USP_Digitaltion_Browse
  @User_Query varchar(500) = NULL,
  @User_Sort varchar(20) = 'Digitaltion_ID',
  @User_Rank varchar(4) = 'Desc',

  @Page_Size int = 24,
  @Page_Order int = 0
AS
  SET NOCOUNT ON
  DECLARE @SQLQuery varchar(1000)
  IF (@Page_Order > 0)
    IF (@User_Rank = 'Asc')
      IF (@User_Query <> '')
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
          WHERE (Digitaltion_ID > (SELECT MAX(Digitaltion_ID) FROM (SELECT TOP '+ STR(@Page_Order * @Page_Size) +' Digitaltion_ID FROM [DigitalART_Digitaltion] WHERE ('+ @User_Query +') ORDER BY '+ @User_Sort +' Asc) AS TeamTable)) AND ('+ @User_Query +')
          ORDER BY '+ @User_Sort +' Asc
          FOR XML EXPLICIT'
        END
      Else
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
          WHERE (Digitaltion_ID > (SELECT MAX(Digitaltion_ID) FROM (SELECT TOP '+ STR(@Page_Order * @Page_Size) +' Digitaltion_ID FROM [DigitalART_Digitaltion] ORDER BY '+ @User_Sort +' Asc) AS TeamTable))
          ORDER BY '+ @User_Sort +' Asc
          FOR XML EXPLICIT'
        END
    Else
      IF (@User_Query <> '')
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
          WHERE (Digitaltion_ID < (SELECT MIN(Digitaltion_ID) FROM (SELECT TOP '+ STR(@Page_Order * @Page_Size) +' Digitaltion_ID FROM [DigitalART_Digitaltion] WHERE ('+ @User_Query +') ORDER BY '+ @User_Sort +' Desc) AS TeamTable)) AND ('+ @User_Query +')
          ORDER BY '+ @User_Sort +' Desc
          FOR XML EXPLICIT'
        END
      Else
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
          WHERE (Digitaltion_ID < (SELECT MIN(Digitaltion_ID) FROM (SELECT TOP '+ STR(@Page_Order * @Page_Size) +' Digitaltion_ID FROM [DigitalART_Digitaltion] ORDER BY '+ @User_Sort +' Desc) AS TeamTable))
          ORDER BY '+ @User_Sort +' Desc
          FOR XML EXPLICIT'
        END
  Else
    IF (@User_Rank = 'Asc')
      IF (@User_Query <> '')
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
   WHERE ('+ @User_Query +')
          ORDER BY '+ @User_Sort +' Asc
          FOR XML EXPLICIT'
        END
      Else
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
          ORDER BY '+ @User_Sort +' Asc
          FOR XML EXPLICIT'
        END
    Else
      IF (@User_Query <> '')
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
   WHERE ('+ @User_Query +')
          ORDER BY '+ @User_Sort +' Desc
          FOR XML EXPLICIT'
        END
      Else
        BEGIN
          Set @SQLQuery =
          'SELECT TOP '+ STR(@Page_Size) +'
          1 AS Tag,
          NULL AS Parent,
          Digitaltion_ID AS [row!1!id!element],
          Digitaltion_Title AS [row!1!title!element],
          Digitaltion_SmallThumbnail AS [row!1!thumb!element]
          FROM [DigitalART_Digitaltion]
          ORDER BY '+ @User_Sort +' Desc
          FOR XML EXPLICIT'
        END
  SELECT '<DigitalARTBrowse>'
  EXECUTE (@SQLQuery)
  SELECT '</DigitalARTBrowse>'
GO