What would be the most efficient way to query multiple records using a named query? Say I have a table with an integer primary key and I want to get record 1, 6, and 14. It seems expensive to me to call the named query three separate times with each of the primary keys. I see there is a post where someone sends in a string with the three primary keys and uses a table value function to split the string. What if the database does not have that table value function and I do not have access to create that function on the database side?
Pretty much every DB out there supports the IN operator. Unfortunately, you can’t pass a single object to it in a SQL-injection-proof way. If you are stuck with a binding that needs a single named query, then you’d use a queryString parameter for the comparison and value. Something like this:
Select *
From MyTable
Where myKey {condition}
You would arrange the binding to supply (as a string) either = 1 for known single responses or IN (1, 6, 14) for your multiple responses.
2 Likes
I have used @pturmel 's suggestion before, and one thing that simplified it for me when I had a python list of values that I wanted to use in my WHERE clause was to do this:
#arbitrarily sized list of values
myList = [1,4,16,...]
queryString = 'IN {}'.format(str(tuple(myList)))
That’s right. I forgot about the QueryString setting in named queries. That is exactly what I need.