multiple comma deliimited parameters

Question

Using this code as a base, I can use this example as an AND for multiple criteria, using multiple INNER JOINs:

Declare @Ids varchar(50)
Set @Ids = '1,2,3,5,4,6,7,98,234'

declare @Levels varchar(50)
set @levels='0,3'

declare @XMLLevels XML
SET @XMLLevels = CAST('<i>' + REPLACE(@Levels, ',', '</i><i>') + '</i>' AS XML)

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT a.* 
FROM
    [AdventureWorks2012].[HumanResources].[Employee] a
    INNER JOIN @XML.nodes('i') x(i) 
        ON  a.[BusinessEntityID] = x.i.value('.', 'VARCHAR(MAX)')
    INNER JOIN @XMLlevels.nodes('i') y(i) 
        ON  a.[OrganizationLevel] = y.i.value('.', 'VARCHAR(MAX)')

I can't seem to work out what joins I would need for an OR function

Don't ude comma delimited strings in sql. Just don't go there. I mean it. And if you have to, use Jeff Moden's splitter. — Zohar Peled, Mar 08 '18 at 22:28
@ZoharPeled, I don't have a choice. That's how things are passed to the routine. I just have to make the best of it, and as there are 10+ items for slicing things up, a stored procedure makes it interminably slow — SeanC, Mar 08 '18 at 22:35

score 1 · Answer 1 · answered Mar 08 '18 at 22:01

Try something like...

Declare   @Ids varchar(50)    = '1,2,3,5,4,6,7,98,234'
        , @Levels varchar(50) = '0,3';

SELECT  a.* 
FROM    [AdventureWorks2012].[HumanResources].[Employee] a
Where a.[BusinessEntityID]  
            IN (
                SELECT  CAST(RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS INT) IDs
                FROM (
                        SELECT Cast ('<X>' 
                                    + Replace(@Ids, ',', '</X><X>') 
                                    + '</X>' AS XML) AS Data
                        ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a) 
                    )
OR a.[OrganizationLevel]   --<-- You can "OR" or "AND" here 
            IN  (
            SELECT CAST(RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS INT) [Levels]
                 FROM (
                        SELECT Cast ('<X>' 
                                    + Replace(@levels, ',', '</X><X>') 
                                    + '</X>' AS XML) AS Data
                        ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a) 
                    )

Good answer (+1 from my side). It might be easier to use `Split.a.value('text()[1]', 'int')` (without the CAST and the trims). if this is an int, you get it directly, if not, it will break in any case... — Shnugo, Mar 09 '18 at 18:00

multiple comma deliimited parameters

1 Answers1