Using in () on a query with a data type of integer and multiple items

I have this query:

delete from template_items
where template_item_id in ({{BulkDeleteRows.value}})

BulkDeleteRows is a comma delimited list of IDs I get from a multiselect table.

If one item is selected and thus just a single value in BulkDeleteRows, it all works.

However, if I have multiple rows selected I get this error: Conversion failed when converting the nvarchar value ‘19,18’ to data type int. So somewhere something is assigning single parameter to the contents of my in() and typing it as an int.

I can easily get around this with a stored procedure, but thought I’d throw this out there as it does not seem like an expected result.

What type of resource are you encountering this on? I know that each SQL variant handles this syntax a bit differently, so there may be something we can do in the query itself to get this working. Otherwise, do you get the same error when trying this query in GUI mode:

I am using Azure SQL Server.

I have it working on a stored procedure, but refactored it to see if using IN in GUI mode would work (I did not know about using IN here before) and that worked.

Thanks!

No problem, thank you for testing that out!

I think it comes down to making sure that the query is interpolated properly by Retool, which is just a matter of finding the right syntax. I’ll test some things out on my end to try to find the right syntax for SQL mode, for future reference.

OK, I was able to get this working with the following syntax:

delete from dbo.users
where id in(SELECT value FROM STRING_SPLIT({{ BulkDeleteRows.value }}, ‘,’))

:grinning: That is almost exactly what my stored procedure looks like! Didn’t think to try that directly in Retool. There is a reason that after doing this for a few decades my forehead has a palm shaped impression in it.

1 Like

Haha no worries! Glad we could get this sorted out!