Be Careful when using Table-Valued Parameter

Recently we found two implicit traps when using Table-Valued Parameter, which may easily lead to unexpected behaviors.

First is that it needs additional precision/scale information when passing decimal type data, otherwise the decimal data would be rounded like long type value. It is very hidden as there is no exception thrown, and we can only find it by manually checking the data. In the meantime, as ADO.Net DataTable class doesn't contain precision/scale information, it means you cannot directly pass the DataTable as the parameter. You may either use IEnumerator<SqlDataRecord> or use DbDataReader created from DataTable and reset the schema table. Refer to this article which also discusses such issue.

Second is that the data is populated by position instead of column names, although you have column names defined in DataTable/DbDataReader and column names defined in table type. As there is no exception thrown, it is hard to realize if the data is corrupted for incorrect orders. Refer to this article which also discusses this issue.