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, ','))

Those aren't arrays. They are strings that you try to split. If you wanted to use multiple values you could have used table-typed parameters or variables. In fact, you should do so with this query instead of splitting strings inside the query, just to make it easier to understand — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 21, 2018 at 11:54
Use a string splitter (String_split (Transact-SQL), delimitedsplit8k_lead,XML Splitter) or use a table type variable/parameter. — Thom A
– Thom A ♦, Commented Nov 21, 2018 at 11:54
@Larnu the code already uses STRING_SPLIT inside the query. "Arrays" is the least of the problems, the query needs some serious cleaning — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 21, 2018 at 11:55
Lay out your code better, then you can see levels of nesting. And things like your mistake don't get hidden in the mess... WHERE c.[Name] = @City — MatBailie
– MatBailie, Commented Nov 21, 2018 at 12:41

MatBailie · Accepted Answer · 2018-11-21 13:30:44Z

2

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)

edited Nov 21, 2018 at 13:30

answered Nov 21, 2018 at 12:47

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cristi Priciu Over a year ago

Thank you Matt! Just one small thing: c.IdCustomer should be o.IdCustomer.

Clay Over a year ago

@CristiPriciu, notice how easy mistakes are to find when the code is laid out properly.

Boris Jovanovic · Accepted Answer · 2018-11-21 12:13:36Z

0

I would use table variables to solve this, example:

declare @temp table (value nvarchar(255))
insert into @temp values ('value1'), ('value2') --,('value3')...

In order to use the "filter" tables in the query you would simply do the following:

select * from [table_name] where [column_name] in (select value from @temp)

Hope this helps.

answered Nov 21, 2018 at 12:13

Boris Jovanovic

13210 bronze badges

Collectives™ on Stack Overflow

SQL Server WHERE IN two different arrays

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related