Query thought in sets?

Question

I have a task that looks like this:

• I have a database with the table - DressSizes
• I have a xml also with dress sizes. This xml is passed as a parameter to the function.
• My goal is to get the data from the table according to the xml which is passed.

OUTPUT:

1. if my table contains sizes which are present in xml, I should select the smallest common size.
2. if my table doesn't contain sizes which are present in xml, i should select the smallest size from the table.
3. if i have passed null to the function 2nd must happen.

I already have a working query, that looks like this:

ALTER  function [dbo].[DressAvialableSizez]
(
    @DressID int,
    @ListXml xml = NULL
)
RETURNS TABLE 
AS
RETURN
(
    WITH CTE_SizeFilter as
    (
        SELECT Xmldata.Element.value('.','varchar(5)') AS Sizes, S.SortIndex AS FSort, S.DressID
              FROM @ListXml.nodes('/list/i') AS Xmldata (Element)
              INNER JOIN DressesSizes AS S ON S.DressSize = Xmldata.Element.value('.','varchar(5)')
        WHERE S.DressID = @DressID
    )

    SELECT TOP(1) F.Sizes, F.FSort, S.DressSize, S.SortIndex, S.DressID
    FROM DressesSizes AS S
    LEFT JOIN CTE_SizeFilter AS F ON S.DressSize = F.Sizes
    WHERE S.DressID = @DressID AND S.DressSize = IIF( EXISTS (
                        SELECT F.Sizes
                        FROM DressesSizes AS S
                        INNER JOIN CTE_SizeFilter AS F ON S.DressSize = F.Sizes),
                        F.Sizes,
                        S.DressSize)
    order by F.FSort, S.SortIndex
)

PROBLEM:

I showed my code to my supervisor but he/she keeps saying that it can be simplified and i need to think in sets. I seem to struggle in understaning how to "think in sets" and no googling didn't help. So what can I do, I'm lost.

Answer 1

Try something like this:

WITH CTE_SizeFilter as
(
    SELECT Xmldata.Element.value('.','varchar(5)') AS DressSize
    FROM @ListXml.nodes('/list/i') AS Xmldata (Element)
)

SELECT TOP(1) S.DressSize, S.SortIndex, S.DressID
FROM DressesSizes AS S
LEFT JOIN CTE_SizeFilter AS F ON S.DressSize = F.DressSize
WHERE S.DressID = @DressID
ORDER BY CASE WHEN F.Sizes IS NOT NULL THEN 1 ELSE 2 END, S.DressSize

This simply selects the smallest dress size, but prioritizes those that are present in the XML.

PS. It's not clear to me what you should do with the @DressID parameter.

Later edit: I have used the @DressID parameter, considering the revised question.