How to specify a parameter as a collection in Commandtext

Question

I use SqlCommandProvider and I need to get some data for each identifier in the identifier collection

let ids=["B058A99-C4B2-4CC3-BA9F-034B1F1ECCBD";"A09C01C-D51B-41C1-B44C-0995DD285088"] [<Literal>] let qLogo ="""SELECT Id,LogoUrl FROM Hotels WHERE Id IN (@IDS)""" let queryLogo = new SqlCommandProvider<qLogo,constring>() queryLogo .Execute(ids)//i need to be able to pass a collection here 

`

Answer 1

In short, SqlCommandProvider is not even the right type provider for this (assuming you are not considering string concatenation to build a query). The reason is that SQL Server only accepts array parameters, calling the stored procedure and passing the Table Valued parameter.

So, to call the stored procedure you will need SqlProgrammabilityProvider for this. But you will need to create a table type and a stored procedure up, as described in the type of vendor documentation (scroll down to "Parameter Table (TVPs)".

Relevant discussion: How to use the SQL IN statement in fsharp.data.sqlclient?