3 ms·
> What about `WHERE foo IN (?,?,?)`. This is can be automatically handled if your ORM supports it; even a micro-ORM like Dapper does. Otherwise my go-to solut
by GenericsMotors 9y ago
> What about `WHERE foo IN (?,?,?)`.
This is can be automatically handled if your ORM supports it; even a micro-ORM like Dapper does.
Otherwise my go-to solution for this if it isn't supported is to pass the collection as a user-defined table type, filled with the values. you can either use WHERE IN or join on this table variable.
EDIT: my perspective on this if from working with SQL Server and .NET. I don't know enough about Python or PHP development to comment on those.
- dotancohen 9y agoIf you're using an ORM then you don't worry about creating the prepared statement or SQL string yourself, so the entire issue is moot (See GP post).
- GenericsMotors 9y agoIt's not moot: Dapper doesn't auto-generate the query for you, it's just a thin layer over .NET's SqlClient to reduce boilerplate of converting to and from C# objects. You still have to write your SQL statements, and in your example you'd still be able to refer to your array by name: select SomethingID where AnotherThing in @yourNamedArrayParameter More here: https://github.com/StackExchange/Dapper#list-support https://github.com/StackExchange/Dapper#list-support If you're not using Dapper just use a user-defined table type to hold your data and join on it instead of using WHERE IN.
- dotancohen 9y agoVery cool, thank you!