Original data type reference when using Sql_Variant

Question

I am creating a table with an SQLVariant data type. The values stored within the variant column could be string, integer, and/or datetime. I read about the pitfalls of sqlvariant, but as the column isn't being indexed, or used in WHERE clauses, it seems the best choice. However, I need to store a reference to the appropriate data type that the SqlVariant should be cast from/to.

In Microsoft .Net (my application), each SQL data type has an underlying numeric value:

I can't see an equivalent system in TSQL, so was wondering how I reference what each data type should be when using it. Is there an "ID" for each TSQL data type?

The reason for this requirement is that the client passes the values as XML to a procedure, which saves the values into the table, e.g.

<parameters>
    <p ID="1" Value="2017-04-28" Type="?????" /> Perhaps the SqlDbType integer value from above (4), or an equivalient TSQL identifier?
    <p ID="2" Value="123" Type="Integer" />
    <p ID="3" Value="123.456" Type="Double" />
    <p ID="4" Value="Foo Bar" Type="String" />
</parameters>

Should I just use the SqlDbType value from .Net, or is there a better way?

UPDATE

Think I might have found something...

SELECT * FROM sys.types

Is system_type_id the value I need here please?

Mikhail Lobanov · Accepted Answer · 2017-04-28T09:50:24.257

2

You can create a user-defined table type to pass array of parametes as table-valued parameter:

SQL Code: CREATE TYPE MyType AS TABLE (ID int, Value Sql_Variant)

CREATE PROCEDURE SP_NAME 
    @Values dbo.MyType READONLY
AS
    --@Values is usual table variable (but readonly) and you can select from it like from tables in database
    INSERT INTO SomeDBTable
    SELECT * 
    FROM @Values V
    WHERE V.ID <= 100500

.NET Code

DataTable dtList = new DataTable();
List<SqlDataRecord> filterList = new List<SqlDataRecord>();
foreach (KeyValuePair<string, object> filter in arrFilterList)
{
    SqlDataRecord record;

    record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("ID", SqlDbType.Int),
                                      new SqlMetaData("Value", SqlDbType.Variant) });
    record.SetInt(0, filter.Key);
    record.SetValue(1, filter.Value);
    filterList.Add(record);
}
SqlCommand oCommand = new SqlCommand("SP_NAME", connection);
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.Parameters.AddWithValue("@Values", filterList.Count > 0 ? filterList : null);
oCommand.Parameters["@Values"].SqlDbType = SqlDbType.Structured;
oCommand.Parameters["@Values"].TypeName = "dbo.MyType";

edited Apr 28 '17 at 09:50

answered Apr 28 '17 at 09:20

Mikhail Lobanov

2,976
9
24

1

There also a table of type mappings, but table-valued type is better than xml https://msdn.microsoft.com/en-US/library/cc716729%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 – Mikhail Lobanov Apr 28 '17 at 09:24
@EvilDr I wrote my answer because I think that passing paramters in ADO.NET in xml is bad practice – Mikhail Lobanov Apr 28 '17 at 09:27
I appreciate that, thank you. Won't your .Net code be translated to a string-based EXEC SP_NAME anyway, which is essentially the same thing (the parameters being sent as text)? – EvilDr Apr 28 '17 at 09:33
1

TVP are passed in native format, not a string. if your command have `CommandType = CommandType.StoredProcedure` there no `EXEC SP_NAME` string. ADO.NET will use RPC to execute stored procedure – Mikhail Lobanov Apr 28 '17 at 09:37
1

Profiler will show it as EXEC, but it's only visualisation of a query – Mikhail Lobanov Apr 28 '17 at 09:38
So you created your own data base type to run with this, correct? – EvilDr Apr 28 '17 at 09:41
1

Yes, I've created it. Sometimes creating datatypes is a big problem. When you want to change it you should drop all objects that references it before. – Mikhail Lobanov Apr 28 '17 at 09:43
Can you please expand your code to show how you retrieve the data from `filterList` in your stored procedure? I've got a lot of learning to do here I think... – EvilDr Apr 28 '17 at 09:46
I've added example - you can use it like usual table (but readonly) – Mikhail Lobanov Apr 28 '17 at 09:51
Sorry, but could you please post the definition of your `MyType ` SQL object. I'm just reading about `SqlDataRecord`on MSDN, which seems like a really smart way of doing things – EvilDr Apr 28 '17 at 10:00
1

In the beginning: `CREATE TYPE dbo.MyType AS TABLE (ID int, Value Sql_Variant)` – Mikhail Lobanov Apr 28 '17 at 10:40
You can also pass DataTable instead of IEnumerable, but I can't make it work with `Sql_Variant`. But with other structures (array of IDs for example) DataTable easier than IEnumerable – Mikhail Lobanov Apr 28 '17 at 10:42

Original data type reference when using Sql_Variant

UPDATE

1 Answers1