IF subquery needs to test several values

Question

How do I split a string so I can access item x? - the solutions have used CTE and WHILE. I told my request was not using WHILE because this is not good performance, it delays the speed.. same goes for CURSOR

I am using SQL Server 2012. I will get a string of ids (@IDs) that needs to be tested against a value (in this case: 1).

IF (SELECT CycleId
    FROM TABLE1
    WHERE ID in (@IDs) ) = '1'
BEGIN
     -- ABORT TRANSACTION AND FINISH THE QUERY
END
ELSE
BEGIN
     -- START TRANSACTION FOR THE Id....
END

This does not work. I was told that using a WHILE or CURSOR should be avoided.

I need to run the several values present in @Ids (it can be 100 values or just 5 values separated by comma , ). If, at least one, of these Ids have CycleId=1 it should stop completely the operation. Maybe it could be...

BEGIN          
      RAISERROR ('It is not possible to complete the transaction.',20,1)
      BREAK
END

1st case:

@IDs = 121, 434, 543, 345

Id    CycleId
---------------
121      1
434      2
543      1
345      1

in this case the operation should stop at @Id 121 (it should not read/test any other Ids and it should throw an error like I have shown before)

2nd case

@IDs = 121, 434, 543, 345

Id    CycleId
--------------
121      3
434      2
543      5
345      6

In this case the operation should do the transaction.

Thanks!

What's your sql-server version? and could you provide some sample data and expect result? — D-Shih, Sep 03 '18 at 14:10
Possible duplicate of [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Bridge, Sep 03 '18 at 14:30
https://www.sqlshack.com/sql-server-cursor-performance-problems/ here "SQL Server cursors are notoriously bad for performance. In any good development environment people will talk about cursors as if they were demons to be avoided at all costs. The reason for this is plain and simple; they are the best way to slow down an application." do you disagree? — graphene, Sep 03 '18 at 15:47

Carsten Massmann · Answer 1 · 2018-09-03T14:35:42.750

Assuming your string variable @ids somehow looks like this: ' 2, 4 , 5 , 7,8,9,10, 21,22', you could try the following:

-- pre-condition @ids by removing blanks and adding a comma at either end:
select @ids=','+replace(@ids,' ','')+',';

IF (SELECT 1 FROM TABLE1 WHERE patindex(concat('%,',CycleId,',%',@ids)>0 )
BEGIN
 -- ABORT TRANSACTION AND FINISH THE QUERY
END
ELSE
BEGIN
 -- START TRANSACTION FOR THE Id....
END

score 1 · Answer 2 · answered Sep 03 '18 at 14:25

Your first problem is that you're trying to match an integer inside a VARCHAR list of comma delimited values. There is a hacky solution for this but you should specify a table variable instead of CSV:

SELECT 1
FROM table1
WHERE CONCAT(',', @IDs, ',') LIKE CONCAT('%,', ID , ',%')
AND CycleId = 1

Wrap the above inside an EXISTS clause:

IF EXISTS (SELECT ...)
BEGIN
  RAISERROR ...
END

score 1 · Answer 3 · answered Sep 03 '18 at 14:33

You can try this.

using xml node to spilt your variable by , comma, then get the result set.

the use exists to compare the condition.

declare @IDs varchar(100) = '121,434,543,345' 

create table TABLE1(
    Id int,
    CycleId int
);


insert into TABLE1 values (121,1);
insert into TABLE1 values (434,2);
insert into TABLE1 values (543,1);
insert into TABLE1 values (345,1);

IF exists( 
    SELECT 1
        FROM TABLE1 t1
        WHERE ID  in (
            SELECT Split.a.value('.', 'NVARCHAR(MAX)')
            FROM
            (
                SELECT CAST('<X>'+REPLACE(@IDs, ',', '</X><X>')+'</X>' AS XML) AS String
            ) AS A
            CROSS APPLY String.nodes('/X') AS Split(a)
            WHERE  t1.CycleId = 1
    )
)

BEGIN
     -- ABORT TRANSACTION AND FINISH THE QUERY
END
ELSE
BEGIN
    -- START TRANSACTION FOR THE Id....
END

sqlfiddle

i see that @ids after IN will not work (after the BEGIN). I must copy like: SELECT Split.a.value('.', 'NVARCHAR(MAX)') FROM ( SELECT CAST(''+REPLACE(@Ids, ',', '')+'' AS XML) AS String ) AS A CROSS APPLY String.nodes('/X') AS Split(a) is there another way to prevent duplication of both queries inside the two IN? — graphene, Sep 05 '18 at 12:46

score 0 · Answer 4 · answered Sep 06 '18 at 15:26

up:

USING Sik.D answer to complete the remaining.. a new Id enters in @id=4956 and does not exist in the table... hence the t1.CycleId IS NULL. This repeats a lot:

 "SELECT Split.a.value('.', 'NVARCHAR(MAX)')
                             FROM  ( SELECT CAST('<X>'+REPLACE(@IDs, ',', '</X><X>')+'</X>' AS XML) AS String) AS A
                             CROSS APPLY String.nodes('/X') AS Split(a))

"

and this is the final code:

IF exists( 
        SELECT 1
        FROM TABLE1 t1
        WHERE ID  in (
            SELECT Split.a.value('.', 'NVARCHAR(MAX)')
            FROM
            (
                SELECT CAST('<X>'+REPLACE(@IDs, ',', '</X><X>')+'</X>' AS XML) AS String
            ) AS A
            CROSS APPLY String.nodes('/X') AS Split(a)
            WHERE  t1.CycleId = 1
    )
)



BEGIN -- there should be some ids without t1.CycleId -- assuming exists is true if there is one positive row for t1.CycleId=1
     IF NOT EXISTS  (SELECT 1
                 from table 1
                 where id in ( **-- same as before*  WHERE t1.CycleId IS NULL) )

        begin
             INSERT INTO table1
             values (id,1) -- not sure here with the Id...
         end

   ELSE


BEGIN
     IF NOT EXISTS  (SELECT 1
                     from table 1
                     where id in ( **-- same as before*  WHERE t1.CycleId IS NULL) )

            BEGIN 
                 INSERT INTO table1
                 values (id,1) -- not sure here with the Id...
            END 

                ELSE 

            BEGIN
                UPDATE table1 
                SET CycleId=1 
                WHERE ID IN (SELECT Split.a.value('.', 'NVARCHAR(MAX)')
                             FROM  ( SELECT CAST('<X>'+REPLACE(@IDs, ',', '</X><X>')+'</X>' AS XML) AS String) AS A
                             CROSS APPLY String.nodes('/X') AS Split(a))

            END
END

How to simplify? thanks.

IF subquery needs to test several values

4 Answers4