Passing parameter of type 'object' in table-valued parameter for sql_variant column

Question

I have a table-valued parameter in SQL Server 2012 defined as:

CREATE TYPE [dbo].[TVP] AS TABLE (
    [Id] [int] NOT NULL,
    [FieldName] [nvarchar](100) NOT NULL,
    [Value] [sql_variant] NOT NULL
)

I call it in C# with code that looks roughly like the following:

var mdItems = new DataTable();
mdItems.Columns.Add("Id", typeof(int));
mdItems.Columns.Add("FieldName", typeof(string));
mdItems.Columns.Add("Value", typeof(object));
mdItems.Rows.Add(new object[] {2, "blah", "value"}); //'value' is usually a string
SqlCommand sqlCommand = conn.CreateCommand();
sqlCommand.CommandText = "[WriteFieldValues]";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@FieldValues", mdItems);
sqlCommand.ExecuteNonQuery();

I then get the following error from SQL Server on the ExecuteNonQuery call:

The type of column 'Value' is not supported. The type is 'Object'

I found someone who encountered the same problem 3 years ago when it was identified as a known Microsoft bug. The link to the bug is broken, though. Does anyone know if there is updated information on the status of the bug or a potential workaround? As it stands, this bug really kills the value of sql_variant fields.

Arianna

@JonSeigel I've added the C# calling code. – Arianna

what is the reason you chose sql_variant? Just curious. – Shortage

@Shortage The field needs to store string, date, and numeric values. I could do the manual conversions in code, but that's what sql_variant is supposed to do for me. – Arianna

@Dan How did you end up handling this? – Aspidistra

@UriAbramson I changed the TVP to have separate typed columns for each possible type of value. Undesirable and ugly, but so far as I could tell, unavoidable if I wanted to use TVPs. – Arianna

The mentioned MS Connect link seems to be OK at the moment; the bug is not fixed. – Mesotron

Answer 1

This post is many years old now but I hit the same problem and have a solution. If you do not use a DataTable but instead populate a collection of SqlDataRecord then you can set the datatype of the SqlDataRecord to SqlDbType.Variant.

 List<SqlDataRecord> dataTable = new List<SqlDataRecord>();
var dr = new SqlDataRecord(
                            new SqlMetaData("Id", SqlDbType.Int),
                            new SqlMetaData("Value", SqlDbType.Variant));

dr.SetInt32(0, id);
dr.SetValue(1, myObject);

dataTable.Add(dr);

[...]

SqlCommand sqlCommand = new SqlCommand("dbo.MyProc");
var structuredParam = sqlCommand.Parameters.Add("myTableParam", SqlDbType.Structured);
structuredParam.Value = dataTable;

Shearin

Answer 2

Unfortunately, I don't have time right now to completely answer this, but I can get you on the right path.

What I have done in the past is, rather than using a TVP, use an XML parameter, serialize the dataset (or any POCO, for that matter)as XML, pass that XML into the proc, then use the ".nodes" property (and other members) of the xml variable to extract whatever was needed into temp tables, local table vars, "work tables" etc...

That's vague, but if you serialize that dataset, and inspect the xml that's created, and read up on XML Data Types in Books On Line, you will likely be able to figure out how to complete your task.

If that doesn't help, I will try to put together an actual solution tomorrow, as I just came across this question as I was leaving for the day.

Cheers!!

Diverting

Answer 3

I'm guessing that you're using sql_variant because you want to pass in different types.

You should use nvarchar(255) instead and then pass in the type to the table.

CREATE TYPE [dbo].[TVP] AS TABLE (
    [Id] [int] NOT NULL,
    [FieldName] [nvarchar](100) NOT NULL,
    [Value] [nvarchar](255) NOT NULL,
    [Type] [nvarchar](255) NOT NULL
)

Also use typeof(string) instead of typeof(object) for the Value column in your DataTable.

This way you can cast the [Value] to the [Type] inside the stored procedure.

SELECT CAST([Value] AS [Type]) AS ValueWithType

Phlegmy

Answer 4

Hopefully, you have a really good use for this "one size fits all" data model. Typically, it blows up in your face when you try to scale due to a misunderstanding of set based relational algebra and sargeability. Maybe you've given this some thought. I would hate to develop ETLs or reports on this model.

Keep in mind, you are trying to insert an object into the database engine that it has no knowledge of the underlying structure. The object is so generic that the receiver of the object has no knowledge of how to interpret it. The object has properties, but you'll only be able to access those if you have explicit instructions or you use reflection to identify the underlying type to unpackage them. The only way (that I know of) to reliably convert an object to the underlying type, without knowing anything about it, is through the use of reflection. Maybe some of the .Net gurus will fill me in on other ways.

The generic object type cannot be stored as a sql_variant like this. Even sql_variant requires somewhat strongly typed values. SQL Server is not going to implicitly use reflection to try and figure out what the data type (and then the value of the underlying data) is.

You could use System.Reflection's GetType method and then throw a case statement in to cast accordingly before insert.

Jequirity

This TVP and proc is on the periphery of my data model and is not in a performance-critical or search-intensive area. In this particular case, I wanted SQL Server to manage the type conversions for me rather than rolling my own. – Arianna