SQL Server WHERE IN two different arrays

Question

Please help me learn SQL! In the code below, on the last line, I have a WHERE that should filter the data according to the elements in the arrays. This works fine if the arrays have just one element, but for more it gives 0 results. The arrays come into the stored procedure from the @Region and @City parameters.

How can i make this code work with multiple elements in the arrays?

DECLARE @act nvarchar(50)  = 'salesbyregionandcity';
DECLARE @Region nvarchar(MAX) = 'Marrakech-Safi,Rabat-Salé-Kénitra';
DECLARE @City nvarchar(MAX) = 'Marrakesh,Salé';

IF(LOWER(@act) = 'salesbyregionandcity')
    SELECT
        r.[Name] AS RegionName, x.CityName, x.Amount, x.[Year]
    FROM
    (
        SELECT
            c.IdRegion, c.[Name] AS CityName, b.Amount, b.[Year]
        FROM
        (
            SELECT
                 ISNULL(c.IdCity, 1) AS IdCity, a.IdCustomerPos, 
a.Amount, a.[Year]
            FROM
            (
                SELECT
                    IdCustomerPos, Amount, Year([Date]) AS [Year]
                FROM
                    Invoice
                WHERE
                    YEAR([Date]) = YEAR(getdate())
                 OR YEAR([Date]) = YEAR(getdate()) - 1
            )
                AS a
            LEFT JOIN
                CustomerOffices c
                    ON a.IdCustomerPos = c.IdCustomer
        )
            AS b
        LEFT JOIN
            City c
                ON c.Id = b.IdCity
        WHERE
            c.[Name] = @City
    )
        AS x
    LEFT JOIN
        Region r
            ON x.IdRegion = r.Id
    WHERE
        r.[Name]   IN (SELECT * FROM STRING_SPLIT (@Region, ','))
    AND x.CityName IN (SELECT * FROM STRING_SPLIT (@City, ','))

MatBailie · Accepted Answer

Remove the following...

    WHERE
        c.[Name] = @City

Your @city variable is a comma delimited list handled in the outer most WHERE clause. I'm guessing this is a hold over from before you made it in to a "list".

If you had laid out your code less "compactly" it would have been easier to spot.

Also, as NULL can never be IN() anything, your LEFT JOINs should really be INNER JOINs.

On the topic of formatting, there is absolutely no need for all that nesting.

SELECT
    r.[Name] AS RegionName,
    c.[Name] AS CityName,
    i.Amount,
    Year(i.[Date]) AS [Year]
FROM
    Invoice           AS i
INNER JOIN
    CustomerOffices   AS o
        ON  i.IdCustomerPos = o.IdCustomer
INNER JOIN
    City              AS c
        ON  c.Id = ISNULL(o.IdCity, 1)
        AND c.[Name] IN (SELECT * FROM STRING_SPLIT (@City, ','))
INNER JOIN
    Region            AS r
        ON  r.id = c.IdRegion
        AND r.[Name] IN (SELECT * FROM STRING_SPLIT (@Region, ','))
WHERE
    YEAR(i.[Date]) = YEAR(getdate())
 OR YEAR(i.[Date]) = YEAR(getdate()) - 1

Two final tips...

It's generally better to JOIN on to the results of STRING_SPLIT() and other "table values functions", as the performance of IN() can dramatically degrade as the size of the list grows...

INNER JOIN
    City              AS c
        ON  c.Id = ISNULL(o.IdCity, 1)
INNER JOIN
    STRING_SPLIT(@City, ',')   AS c_list
        AND c.[Name] = c_list.value
INNER JOIN
    Region            AS r
        ON  r.id = c.IdRegion
INNER JOIN
    STRING_SPLIT(@Region, ',')   AS r_list
        AND r.[Name] = r_list.value

Finally, if you have an index on Invoice(Date), your current WHERE clause can't use it.

As a general rule of thumb, try to avoid putting any calculations on the column you're searching, keep the calculations on the right hand side instead.

For example, the following gets all invoices with dates on or after the 1st January of last year.

WHERE
    i.Date >= DATEFROMPARTS(YEAR(GETDATE())-1, 1, 1)

SQL Server WHERE IN two different arrays

Answers (2)

Related Questions