This is the XML version did not carefully tested and possibly flawed / * Author: SLIGHTBOY Created: 2004-10-18 Modified: memory effect: the role of works Browser tabs database: DigitalART_Digitaltion input variables: Output variables: * / 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 = 0AS Set NoCount on Declare @SqlQuery Varchar (1000) IF (@User_Rank = 'ASC') IF (@user_query <> ') Begin Set @sqlQuery = 'SELECT TOP' STR (@PAGE_SIZE) '1 AS TAG, NULL AS PARENT, DIGITATION_ID AS [Row! 1! ID! Element], DIGITITION_TILE 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, digitation_id as [row! 1! Id! E lement], 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_DID "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, DIGITATION_ID AS [Row! 1! ID! Element], DIGITATION_TILE 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 [ROW! 1! ID! Element], DIGITATION_TILE 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], DIGITATION_TITLE AS [Row! 1! Title! Element], DIGITATION_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], DIGITATION_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) '
