SQL Server: advanced patterns with Table-Valued Parameters When Used in Stored Procedures
I'm writing unit tests and I've been researching this but I've searched everywhere and can't find a clear answer. I'm experiencing an scenario with table-valued parameters (TVPs) in SQL Server 2016. I have a stored procedure that takes a TVP as input to perform batch inserts into a target table. However, when I pass the parameter from my C# application using Dapper, I notice that only the first row from my TVP gets inserted into the target table, and the remaining rows are ignored. Here's the definition of my TVP: ```sql CREATE TYPE dbo.MyCustomType AS TABLE ( Id INT, Name NVARCHAR(100) ); ``` The stored procedure looks like this: ```sql CREATE PROCEDURE dbo.InsertIntoTargetTable @MyTableType dbo.MyCustomType READONLY AS BEGIN INSERT INTO TargetTable (Id, Name) SELECT Id, Name FROM @MyTableType; END ``` In my C# code, I'm using Dapper to call this stored procedure: ```csharp var items = new List<MyCustomType> { new MyCustomType { Id = 1, Name = "Item 1" }, new MyCustomType { Id = 2, Name = "Item 2" } }; var table = new DataTable(); table.Columns.Add("Id", typeof(int)); table.Columns.Add("Name", typeof(string)); foreach (var item in items) { table.Rows.Add(item.Id, item.Name); } using (var connection = new SqlConnection(connectionString)) { connection.Open(); var parameters = new DynamicParameters(); parameters.Add("@MyTableType", table.AsTableValuedParameter("dbo.MyCustomType")); connection.Execute("dbo.InsertIntoTargetTable", parameters, commandType: CommandType.StoredProcedure); } ``` I've double-checked the data being passed to the TVP, and it seems correct, but when I check the `TargetTable` after execution, I only see the first item. Additionally, I do not receive any behavior messages throughout this process. I suspect there may be an scenario with how I'm populating the DataTable or how Dapper is handling the TVP. Has anyone encountered a similar scenario, or could you provide insights on how to troubleshoot this? For context: I'm using C# on Linux. This is part of a larger CLI tool I'm building. Has anyone else encountered this? This is happening in both development and production on Windows 11. Is there a simpler solution I'm overlooking? I'm working in a Ubuntu 20.04 environment. What are your experiences with this?