It seems to me that the parameter expansion in FROM clauses only works for certain parameter types; running the below snippet throws the following exception System.Data.SqlClient.SqlException: 'Incorrect syntax near ','.' when attempting to run a query with a string array parameter.
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var sql = "SELECT ids.id FROM @ids ids (id)";
conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 } }); // This works perfectly
conn.Query(sql, new { ids = new[] { "1", "2", "3", "4", "5" } // This query throws an exception
}
I've struggled to find any documentation regarding this kind of parameter expansion, so my question is whether I've bumped into an undocumented an partially supported feature (perhaps by misusing the parameter expansion), or is this a bug?
As per the hint from Zohar I created an xe-session to capture the statements run by dapper. The parameter expansion is indeed handled very differently for string list parameters
-- first query
exec sp_executesql N'SELECT ids.id FROM (select cast([value] as int) from string_split(@ids,'','')) ids (id)',N'@ids varchar(max) ',@ids='1,2,3,4,5'
-- second query
exec sp_executesql N'SELECT ids.id FROM (@ids1,@ids2,@ids3,@ids4,@ids5) ids (id)',N'@ids1 nvarchar(4000),@ids2 nvarchar(4000),@ids3 nvarchar(4000),@ids4 nvarchar(4000),@ids5 nvarchar(4000)',@ids1=N'1',@ids2=N'2',@ids3=N'3',@ids4=N'4',@ids5=N'5'
Both expansion patterns produce valid syntax for WHERE IN clauses, but not for FROM clauses. So, in the end, partial support for parameter expansion in FROM clauses seems to be something resulting from a misuse of the framework.