MS SQL 2008: A table type with columns of the form sql_variant (for now?) Is incompatible with ADO.NET

sql_variant - A data type that stores the values ​​of various data types supported by SQL Server.

It may be useful in some generalizing aspects of the database being developed. For example, in the application settings table or when maintaining the dynamic properties of an object.

As for ADO.NET, here the possible convenience is that sql_variant can be transferred from managed code to a stored procedure, which is generalized for the transmitted value. If it is necessary to branch the logic according to the value passed, then the actual type can be found through the function SQL_VARIANT_PROPERTY .
')
In fact, there are no problems with a single parameter. Problems arise when you need to pass a list of parameters.



In theory, everything is simple. Judging by this article, it is best to match the list of parameters to a user-defined table type. Keeping in mind that the type of the object in .NET corresponds to the type of the parameter SqlDbType.Variant or DbType.Object , run the management and visual studio.

1. Create a generic type:
   CREATE TYPE [dbo].[GenericList] AS TABLE([value] [sql_variant] NOT NULL)
2. Create a procedure that will handle this parameter:
   CREATE PROCEDURE [dbo].[ParseGenericList]
@list GenericList readonly
AS
BEGIN
SET NOCOUNT ON;
SELECT value from @list
END
   
3. Gladly rubbing the handles in anticipation of the result, we write to the .NET code to transfer the parameter to the database:
   DataTable table = new DataTable("GenericList");
table.Columns.Add("value", typeof(object));

table.Rows.Add("string");
table.Rows.Add(DateTime.Now);
table.Rows.Add(145);

using (SqlCommand command = connection.CreateCommand()) // created before
{
command.CommandText = "dbo.ParseGenericList";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@list", table);
conn.Open();
using (SqlDataReader r = command.ExecuteReader())
while (r.Read())
Console.WriteLine(r.GetValue(0));
}

After the launch, instead of the expected three lines, the bird comes flying in the console as an exception:
"value" . "Object"

To be fair, it’s worth noting that if you change the data type from object \ sql_variant to string \ nvarchar or float \ float, respectively, then everything works with a bang.

With a query to the database, there are also no problems:
declare @list dbo.GenericList;
insert into @list(value) select cast(0 as bit);
insert into @list(value) select cast('bla' as nvarchar(3));
insert into @list(value) select cast(0.15 as float);

exec dbo.ParseGenericList @list

A few minutes of googling find an open report in Microsoft technical support:
clack

Inside the correspondence, the MC gives links to patches that I install. One (for whists) was not established, another (for .net-a) was installed. However, without much result. In the discussion itself, it says that the bug appears.

This is such an unpleasant story. You can try to work around the problem in several ways (crutches):

• Instead of a single generalized sql_variant list, transfer several lists with a specific type (datetime, int, float, etc);
• Pass a list of pairs (string_value, value_type) and cast the values ​​in to sql_variant directly in the stored procedure;
• Make an assumption about the inapplicability of the passed parameters in the CRUD, with the exception of R. Then you can transfer a list of strings and compare the results of castes of the existing sql_variant fields with the transmitted strings;
• Stop using sql_variant in favor of xml structures;

Here is a bummer. Actually, the greatest resentment is caused by a bolt on the problem.