SQL Creating a dynamic IN clause with int values in stored procedure

Question

I have a situation where in I need to create a dynamic IN clause in the query.

Right now the query looks like this:

DECLARE @transactIds varchar(50);
DECLARE @transactionTypeID int = 10;

IF (@transactionTypeID != 0 AND @transactionTypeID = 10)  
BEGIN 
    SET @transactIds = '12'+', '+'20'+', '+'21'+', '+'23'
END    
ELSE    
    SET @transactIds = CAST(@transactionTypeID as varchar(3))
  
SELECT TOP 10 * 
FROM Transactions  
WHERE transactionTypeID IN (@transactIds)

Running this query, I get this error:

Conversion failed when converting the varchar value '12, 20, 21, 23' to data type int.

I know a better approach would be to put these values in temp table and then use select from that temp table inside the IN clause and that is what I am using right now. But using the table approach takes around 20 secs to run the SP. When I use values directly in the in clause it runs in 2 secs. Now, the code shown above is just a part of huge SP that I cannot change, so I need to look for a fix around this only.

Thanks in advance for any help.

`transactionTypeID IN (@transactIds)` doesn't work since transactionTypeID is a integer, and @transactIds a character string. — jarlh, Oct 04 '21 at 19:20
I am using V 17.9. I know that is the issue. this is what I am trying to look a work around for. — Gautam, Oct 04 '21 at 19:22

dogyog · Answer 1 · 2021-11-04T14:02:42.683

You could CAST the string as XML, and then take out the individual integers, adapted from @Shnugo's answer here: extract text from string data using SQL (with handing null values)

DECLARE @Transactions TABLE (transactionTypeID INT)
INSERT INTO @Transactions
VALUES
(12),
(20),
(21),
(23)


DECLARE @transactIds varchar(50);
DECLARE @transactionTypeID int = 10;

IF (@transactionTypeID != 0 AND @transactionTypeID = 10)  
BEGIN 
    SET @transactIds = '12'+', '+'20'+', '+'21'+', '+'23'
END    
ELSE    
    SET @transactIds = CAST(@transactionTypeID as varchar(3))


SELECT TOP 10 * 
FROM @Transactions  
WHERE transactionTypeID  = CAST(CONCAT('<x>',REPLACE(@transactIds,',','</x><x>'),'</x>') AS XML).value('/x[1]/text()[1]','INT')
    OR transactionTypeID  = CAST(CONCAT('<x>',REPLACE(@transactIds,',','</x><x>'),'</x>') AS XML).value('/x[2]/text()[1]','INT')
    OR transactionTypeID  = CAST(CONCAT('<x>',REPLACE(@transactIds,',','</x><x>'),'</x>') AS XML).value('/x[3]/text()[1]','INT')
    OR transactionTypeID  = CAST(CONCAT('<x>',REPLACE(@transactIds,',','</x><x>'),'</x>') AS XML).value('/x[4]/text()[1]','INT')

transactionTypeID
12
20
21
23

EDIT: I found this while working on a similar problem, I'm using it to split a string into smaller strings, but it should work for you as well since the conversion issue is arriving from sending all the integers as one string. https://stackoverflow.com/a/32438728/16307851

One minor error in his answer, he does not include the @SplitOn parameter when he calls the function IE ','.

SQL Creating a dynamic IN clause with int values in stored procedure

1 Answers1