Forum Discussion
Solved
OLEDB positional parameters with a WHERE IN clause
We have FX rates published in a table to/from virtually every currency in the world. However, this is something like 70k rates a month and the vast majority are for currencies we don't use in our One...
Ugh, I finally banged my face on the keyboard enough that I got a relevant search result.
The missing tool was the STRING_SPLIT function. It can be used in the above example, like so:
SELECT FromCurrency,ToCurrency,Rate FROM FxRateTable WHERE (FromCurrency IN (SELECT value FROM STRING_SPLIT(?,',')) AND ToCurrency = 'USD') OR (ToCurrency IN (SELECT value FROM STRING_SPLIT(?,',')) AND FromCurrency = 'USD')Alternatively, you can declare a @variable = ? and then string_split on the @variable instead of passing in both P1 and P2 with the same value.
Ugh, I finally banged my face on the keyboard enough that I got a relevant search result.
The missing tool was the STRING_SPLIT function. It can be used in the above example, like so:
SELECT FromCurrency,ToCurrency,Rate FROM FxRateTable
WHERE (FromCurrency IN (SELECT value FROM STRING_SPLIT(?,',')) AND ToCurrency = 'USD')
OR
(ToCurrency IN (SELECT value FROM STRING_SPLIT(?,',')) AND FromCurrency = 'USD')Alternatively, you can declare a @variable = ? and then string_split on the @variable instead of passing in both P1 and P2 with the same value.
It may be worth pointing out that this is basically a data type fix as the subselect using the string_split function is just turning a string into a data type that the IN clause can use. I still kinda' don't love it as it would feel a lot cleaner (and self-documenting) if I could pass in the right data type to start with and use it natively but it's an effective workaround until someone else comes along and proposes a more "technically correct" solution.