Query thought in sets?

I have a task that looks like this:

a. I have a database with the table - DressSizes b. I have a xml also with dress sizes. This xml is passed as a parameter to the function. c. 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.

1 answer

  • answered 2019-03-13 19:39 Razvan Socol

    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.