USP_SEARCHLIST_SITEIMAGES

CREATE procedure dbo.[USP_SEARCHLIST_SITEIMAGES]
(
  @NAME nvarchar(100) = null,
  @ID integer = null,
  @CATEGORY integer = null,
  @CATEGORIES xml = null,
  @MAXROWS smallint = 500
)
as

  select top(@MAXROWS)
    ID,
    NAME,
    CAPTION
  from dbo.SITEIMAGES
  where
    (@NAME is null or NAME like '%' + @NAME + '%')
    and (@ID is null or ID = @ID)
    and (
      @CATEGORY is null --Disregard categories

      or (@CATEGORY = -1 and ID not in (select SiteImageID from SiteImageFolders)) --Images NOT in a category

      or (@CATEGORY is not null and @CATEGORY > -1 and ID in (select SiteImageID from SiteImageFolders where SiteImageFolders.SiteFolderID = @CATEGORY)) --Images in the specified category

    )
    and (
      @CATEGORIES is null
      or ID in (
        select SITEIMAGEID
        from SITEIMAGEFOLDERS
        inner join @CATEGORIES.nodes('/CATEGORIES/ITEM') T(c) on T.c.value('ID[1]', 'int') = SITEIMAGEFOLDERS.SITEFOLDERID
      )
    )
  order by NAME asc