Document usage of STRING_SPLIT with IN instead of Table-Valued Parameters

[cmeeren]

It seems the consensus on using IN is to explicitly use table-valued parameters, requiring defining types and putting your query in a stored procedure. There's a much easier way that abstracts away the TVP stuff that I suggest should be added to the documentation: Use STRING_SPLIT. Example:

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

(It has to be declared to avoid the same error as described in #309.)

[smoothdeveloper]

@cmeeren, where do you see this addition could be made? maybe the faq.fsx page: https://github.com/fsprojects/FSharp.Data.SqlClient/blob/master/docs/content/faq.fsx

Would you make an initial PR with addition, explaining the pros and cons of each approach?

I'm personally fine with defining stored procedures and table types rather than rely on crude string splitting, even for cases I only use a single column, but I agree users would benefit from knowing about this simple work around involving no ceremony beside the packing of arguments in a string.

Thanks for bringing this up for discussion!

[cmeeren]

Thanks for considering this!

Unfortunately SQL is not my strong suit, so I wouldn't be able to point at potential drawbacks to this method.

Note however that it only applies to SQL Server 2016 and above.

The FAQ might be a good place for this.