I create a table type in SQL:
CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
[Name] VARCHAR(MAX) DEFAULT '',
[Index] VARCHAR(MAX) DEFAULT ''
)
I build a DataTable
and populate it with one DataRow
. I provide that table as a parameter to a stored procedure:
// Set up connection and command variables (code omitted)
// ...
// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");
// Add one row that is missing an Index
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);
// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
TypeName = "MyObject_TableType",
Value = table
};
command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();
I expected that inside of the stored procedure, the index value would default to ''
(empty VARCHAR). Instead, I observed that the value was null. I know this because I attempted to merge the table type into an actual table, and the index column on my table does not allow nulls.
Why doesn't SQL Server honor the default that I placed on my table type? Is there a way to force SQL Server to honor it?
I think I can get around the issue by using the DataTable.DefaultValue property, but that solution has the disadvantage that I need to declare same defaults in two different places. I want to avoid redundant code if possible.
Best Answer
Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows:
Returns:
The reason you are getting
NULL
instead of an empty string is because that is what you are asking the app code to provide. You even (unknowingly, of course ;-) identify the root cause of the problem when stating (emphasis added):A
DataTable
is a representation of an actual Table in memory, not of one or moreINSERT
statements. So when you create that single row, theIndex
column can't be undefined: it is eitherNULL
or some value. And when you pass thatDataTable
into SQL Server as a TVP, you are passing in the equivalent of a Table.Yes, you could probably get around this by specifying a default value for that
DataColumn
in theDataTable
. But I agree that doing this is not the best approach.My preference is to never use
DataTable
s unless you already happen to have one, regardless of using a TVP or not. I can't think of a reason to ever create one simply for the purpose of passing data into a TVP. Besides this particular issue, it also duplicates whatever collection you are placing into it simply to be a temporary transport for the TVP (which wastes memory and the time it takes to copy that data, even if it isn't much of either of those).Instead, create a method that will take whatever collection you already have and simply stream it, one item / row at a time, into the TVP. You do this by returning
IEnumerable<SqlDataRecord>
from this method, and then specify this method as theValue
property of theSqlParameter
representing the TVP.method to stream the collection into the TVP:
using the method:
The trick to getting the Default defined on the UDTT to populate the
Index
column with an empty string is the particular overload of the SqlMetaData Constructor that I used above. It has a boolean parameter foruseServerDefault
. Passing intrue
causes the column to not be passed into theINSERT
statement if not "set" in theSqlDataRecord
. Or, at least that is how I have always gottenIDENTITY
columns in UDTTs to work (never tried it on an actualDEFAULT
, but should be the same behavior).P.S. Why are you using
VARCHAR(MAX)
as the datatype of these two columns in the UDTT? If either, or both, of these columns need to contain SQL Server identifiers (i.e. names of Tables, Views, Stored Procedures, Indexes, etc) then those are allsysname
which is an alias forNVARCHAR(128)
. So you really need to at least be usingNVARCHAR
to avoid potentially data loss / hard-to-trace bug.