Using a querystring parameter in a named query IN clause

Ignition
,
1

I have a query I’m converting to a named query. One parameter is an INT4, another a string.
The string parameter can have one of three values: (‘site1’), (‘site2’) or (‘site1’,‘site2’).
In the named query I am attempting to use the string parameter as follows:

…WHERE a.site IN ‘{site}’

If I test with the value “(‘site1’)” – not sending in the double quotes – it returns this error:
GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘site1’.
I have tested any number of other permutations, but cannot find a way to make this work. I can format the string supplied as a parameter any way necessary, if anyone can advise. thanks

2

Some databases do not accept single-value IN clauses.

3

I neglected to mention it is MS SQL Server 2018. So, a query like

SELECT someColumn FROM someTable WHERE site IN (‘site1’) will work.

Thanks, David

4

Have you tried without the quotes around {site}? These quotes will wrap your passed in list, turning it into a string instead of a list.

And since you are using curly braces, I'm assuming you already have the type set to QueryString for your site parameter?

5

I gave up on fiddling with that and rewrote my query as follows:

SELECT 0 as ‘Select’
,itemId
,itemNumber
,description
,CASE WHEN (SELECT COUNT(*) FROM dbo.v_ivInventory i
INNER JOIN dbo.fnT_SplitVarchar(:site,’,’,‘0’) a ON i.site = a.item
WHERE w.itemId = i.itemId
–and i.site IN ({site}
) > 1 THEN ‘Yes’
ELSE ‘No’ END AS i
FROM dbo.v_imItem w
WHERE itemClassId = :itemClassId
order by i desc, itemId ASC

I changed the parameter from querystring to value, and this works. Thanks.