3 ms·
What about `WHERE foo IN (?,?,?)`. I usually let the language (PHP, Python) handle counting the arguments, but there is no (clean) way to do that with named par
by dotancohen 9y ago
What about `WHERE foo IN (?,?,?)`. I usually let the language (PHP, Python) handle counting the arguments, but there is no (clean) way to do that with named parameters. And there is no option of using some parameters named and others anonymous, so if there is an `IN` anywhere in the query, then the entire query - and any variation of the query - must use anonymous parameters.
- 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!
- Nican 9y agoHere is some simple documentation on how it is done in C#: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx?f=255&mspperror=-2147217396#Anchor_2 https://msdn.microsoft.com/en-us/library/system.data.sqlclie... It is slightly verbose, but I find it immensively valuable. No ORM required.
- dotancohen 9y agoThat documentation does not address the use case that I mention, in which the number of parameters is unknown.