这个是最 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