How to specify a parameter as collection in Commandtext

Question

I am using SqlCommandProvider and I need to get some data for each id in an id 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

Long story short, SqlCommandProvider is not even correct type provider to do this (assuming you don't consider string concatenation to build query). The reason is SQL Server only accepts array parameters through calling stored procedure and passing a Table Valued Parameter.

So, to call a stored procedure you need an SqlProgrammabilityProvider to achieve this. But you'll have to create Table Type and Stored Procedure up-front, as described in type provider documentation (scroll down to "Table-valued parameters (TVPs)" section).

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