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?

Answer 1

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";