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

Question

I have the following sample code. The objective is to run SQL statement with multiple input parameters.

[<Literal>]
let connectionString = @"Data Source=Localhost;Initial Catalog=Instrument;Integrated Security=True"
[<Literal>]
let query = "SELECT MacroName, MacroCode FROM Instrument WHERE MacroCode IN (@codeName)"
type MacroQuery = SqlCommandProvider<query, connectionString>
let cmd = new MacroQuery()
let res = cmd.AsyncExecute(codeName= [|"CPI";"GDP"|]) |> Async.RunSynchronously

However, codeName is inferred to be string type instead of an array or list and give me an error.

Alternatively, I could run the query without where statement and filter based on the result. However, in lots of other cases that returns millions of rows, I would prefer filter data at the SQL server level to be more efficient.

I didn't find any relevant samples on the documentation of fsharp.data.sqlclient. Please help!

• Damien_The_Unbeliever: I assume you're targeting SQL Server. Well, the reason that it's not using an array or list is simple - SQL Server doesn't have such data types. It has one data type specifically designed for storing multiple items of data - the table. I'm not familiar with fsharp.data.sqlclient, but I'd recommend you try to find out how to pass table-valued parameters to it.

Answer 1

"See Table-valued parameters (TVPs)" section in the documentation: http://fsprojects.github.io/FSharp.Data.SqlClient/configuration%20and%20input.html

• Joel Mueller: So you have to put your query into a stored procedure, AND create a custom SQL type just to use an IN statement? Yuck.

Answer 2

If you have an upper bound n on the values in the IN list, you could just make n parameters. If that's unmanageable, I'm afraid the TVP suggestion is your best option. The reason the FSharp.Data.SqlClient library is unlikely to ever support this directly is because the types are generated based on results from sp_describe_undeclared_parameters; there's no T-SQL parser. We had a single digit upper bound in this scenario and were loathe to change the database, so this option worked for us.

Answer 3

You can use STRING_SPLIT to abstract away the use of table-valued parameters. It seems you have to also declare the param first.

DECLARE @param varchar(1000) = @commaSeparatedList
SELECT Col1 FROM MyTable
WHERE Col2 IN (
 SELECT value FROM STRING_SPLIT(@param, ',')
)

• nakedfanatic: This is cool but note that STRING_SPLIT requires compatibility level 130, i.e. SQL Server 2016 minimum.