Styjun

I have a stored procedure that accepts a user-defined Table Type as a parameter. In C# I use SqlDataRecord and SqlMetaData (and SqlParameter) to create a table parameter with my data and send that to the stored procedure.

This works fine when the columns (SqlMetaData) are in the same order as the column definitions within the actual Table Type sql-server. If they are NOT in the same order then the wrong columns will be matched up.

So what is the point of the SqlMetaData.Name property? It seems to be ignored (at least for this use case) and only the order of columns (and data) are considered.

Example...

I have a Table Type in SQL:

CREATE TYPE [dbo].[TestingTableParamType] AS TABLE(
 [FName] VARCHAR(50) NULL,
 [Numb] INT NULL,
 [LName] VARCHAR(50) NULL
);

I have my stored procedure:

CREATE PROCEDURE spTEST_TableParam
@input TestingTableParamType READONLY
AS
SELECT FName, Numb, LName FROM @input

And I have my test data generator method which yields three rows of test data with the appropriate column types in the appropriate order (to match the TestingTableParamType definition). Notice, however, that I use blank strings as names! I tried with nonsense strings and that worked fine also (nulls will cause an exception):

private IEnumerable<SqlDataRecord> genTestSqlDataRecords()

 SqlDataRecord record = new SqlDataRecord(
 new SqlMetaData("", SqlDbType.VarChar, 50),
 new SqlMetaData("", SqlDbType.Int),
 new SqlMetaData("", SqlDbType.VarChar, 50));

 record.SetValues("fn1", 101, "ln1");
 yield return record;
 record.SetValues("fn2", 102, "ln2");
 yield return record;
 record.SetValues("fn3", 103, "ln3");
 yield return record;

One could try specifying the column names and re-ordering the SqlMetaData columns but it will simply use the column order to link up to the Table Type not the column names.

There is some more code that does the database call itself, here is a relevant snippet:

...
SqlParameter param = new SqlParameter("@input", SqlDbType.Structured);
param.Direction = ParameterDirection.Input;
param.TypeName = "TestingTableParamType";
param.Value = genTestSqlDataRecords();

dbCommand.Parameters.Add(param);

conn.Open();

var dataReader = dbCommand.ExecuteReader();
...

So, it seems that the SqlMetaData (column) Name property is irrelevant, at least for this use case. This creates for misleading code - in that it appears like you could reorder the columns (and data) and it should match them up by their names, but that doesn't happen!

Additionally all the examples I've seen of using SqlDataRecord & SqlMetaData, to pass Table Type params, provide names for their columns as if it is necessary.

What have I missed in all of this?
Thank you kindly.

what is the point of the SqlMetaData.Name property?

None whatsoever here. But for a CLR stored procedure streaming a result set via SendResultsStart and related methods, or Send(SqlDataRecord), it's vital because before those methods are called, SQL Server has no idea what the "shape"¹ of the result set is, and it uses this metadata to understand it (and to be able to forward this metadata back to client applications also).

¹I often use shape when talking about result sets. It's the number of columns, their names and their data types.