Sql-server – sp_executesql with user defined table type not behaving correctly

sql serversql-server-2008sql-server-2012

Problem: is there a known issue with user defined table types as parameters to sp_executesql? Answer – no, I'm an idiot.

Set up script

This script creates one each of table, procedure and user defined table type (restricted SQL Server 2008+ only).

The heap's purpose is to provide an audit that yes, the data made it into the procedure. There are no constraints, no nothing to prevent data from being inserted.
The procedure takes as a parameter a user defined table type. All the proc does is insert into the table.
The user defined table type is bog simple as well, just a single column

I have run the following against 11.0.1750.32 (X64) and 10.0.4064.0 (X64) Yes, I know that box could be patched, I don't control that.

-- this table record that something happened
CREATE TABLE dbo.UDTT_holder
(
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)
)
GO

-- user defined table type transport mechanism
CREATE TYPE dbo.UDTT
AS TABLE
(
    ServerName varchar(200)
)
GO

-- stored procedure to reproduce issue
CREATE PROCEDURE dbo.Repro
(
    @MetricData dbo.UDTT READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.UDTT_holder 
    (ServerName)
    SELECT MD.* FROM @MetricData MD
END
GO

Problem reproduction

This script demonstrates the problem and should take five seconds to execute. I create two instances of my user defined table types and then try two different means of passing them in to sp_executesql. The parameter mapping in the first invocation mimics what I captured from SQL Profiler. I then call the procedure without the sp_executesql wrapper.

SET NOCOUNT ON
DECLARE 
    @p3 dbo.UDTT
,   @MetricData dbo.UDTT
INSERT INTO @p3 VALUES(N'SQLB\SQLB')
INSERT INTO @MetricData VALUES(N'SQLC\SQLC')

-- nothing up my sleeve
SELECT * FROM dbo.UDTT_holder

SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing sp_executesql' AS commentary
-- This does nothing
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3
-- makes no matter if we're mapping variables
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

-- Five second delay
waitfor delay '00:00:05'
SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing proc' AS commentary
-- this does
EXECUTE dbo.Repro @p3

-- Should only see the latter timestamp
SELECT * FROM dbo.UDTT_holder

GO

Results: below are my results. The table is initially empty. I emit current time and make the two calls to the sp_executesql. I wait for 5 seconds to pass and emit current time followed by calling the stored procedure itself and finally dump the audit table.

As you can see from the timestamp, the record for the B record corresponds to the straight stored procedure invocation. Also, there is no SQLC record.

ServerName                                       insert_time
------------------------------------------------------------------------

commentary
-------------------------------------------------
2012-02-02 13:09:05.973 Firing sp_executesql

commentary
-------------------------------------------------
2012-02-02 13:09:10.983 Firing proc

ServerName                                       insert_time
------------------------------------------------------------------------
SQLB\SQLB                                        2012-02-02 13:09:10.983

Tear down script

This script removes the objects in the correct order (you can't drop the type before the procedure's reference has been removed)

-- cleanup
DROP TABLE dbo.UDTT_holder
DROP PROCEDURE dbo.Repro
DROP TYPE dbo.UDTT
GO

Why this matters

The code seems silly but I don't have much control over the use of the sp_execute vs a "straight" call to the proc. Higher up the call chain, I am using the ADO.NET library and passing in a TVP as I have done previously. The parameter's type is correctly set to System.Data.SqlDbType.Structured and the CommandType is set to System.Data.CommandType.StoredProcedure.

Why I am a noob (edit)

Rob and Martin Smith saw what I wasn't seeing – the statement being passed to sp_executesql did not use the @MetricData parameter. An unpassed/mapped parameter isn't going to get used.

I was translating my working C# table-valued-parameter code to PowerShell and since it compiled, it couldn't be the code at fault; except it was. While writing up this question, I realized I had not set the CommandType to StoredProcedure so I added that line and re-ran the code but the trace in profiler didn't change so I assumed that was not the issue.

Funny story – if you don't save the updated file, running it won't make a difference. I got in this morning and re-ran it (after saving) and ADO.NET translated it to EXEC dbo.Repro @MetricData=@p3 which works just fine.

Best Answer

sp_executesql is for executing ad-hoc T-SQL. So you should try:

EXECUTE sp_executesql N'exec dbo.Repro @MetricData',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3

Related Solutions

Sql-server – How to list the permissions on User-Defined Table Types

This will list explicitly granted permissions on table types, but not those granted implicitly through role or group membership, or permissions granted against the containing schema.

SELECT 
  [schema] = s.name, 
  [type] = t.name, 
  [user] = u.name, 
  p.permission_name, 
  p.state_desc
FROM sys.database_permissions AS p
INNER JOIN sys.database_principals AS u
  ON p.grantee_principal_id = u.principal_id
INNER JOIN sys.types AS t
  ON p.major_id = t.user_type_id--.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
WHERE p.class = 6; -- TYPE

I am curious what type of explicit permissions you are using in your system for table types? From the documentation there isn't a whole lot you need to implement for standard runtime query support (you can't grant SELECT directly, for example). It seems this is mostly there for metadata / control.

Sql-server – Why are Column Defaults on the User-Defined Table Type (UDTT) not being honored

Why doesn't SQL Server honor the default that I placed on my table type?

Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows:

DECLARE @Test dbo.MyObject_TableType;
INSERT INTO @Test (Name) VALUES ('ObjectOne');
SELECT * FROM @Test;

Returns:

Name        Index
ObjectOne              <--- empty string, not NULL

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):

I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

A DataTable is a representation of an actual Table in memory, not of one or more INSERT statements. So when you create that single row, the Index column can't be undefined: it is either NULL or some value. And when you pass that DataTable 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 the DataTable. But I agree that doing this is not the best approach.

My preference is to never use DataTables 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 the Value property of the SqlParameter representing the TVP.

method to stream the collection into the TVP:

private static IEnumerable<SqlDataRecord> SendRows(List<DbObject> DbObjects)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[]
   {
      new SqlMetaData("Name", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 1),
      new SqlMetaData("Index", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 2)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   int _NumRows = DbObjects.Count;

   // read a row, send a row.
   // "for" should be faster than "foreach", right?
   for (int _Index = 0; _Index < _NumRows; _Index++)
   {
      _DataRecord.SetString(0, DbObjects[_Index].Name);
      _DataRecord.SetString(1, DbObjects[_Index].Index);
      yield return _DataRecord;
   }
}

using the method:

private struct DbObject
{
    public string Name;
    public string Index;
}

List<DbObject> _DbObjects = new List<DbObject>();

// Add one row that is missing an Index    
_DbObjects.Add(new DbObject() { Name = "ObjectOne" });

// Make a parameter for the table
SqlParameter _ParamMyObjects = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType", // not required for CommandType.StoredProcedure
    Value = SendRows(_DbObjects)
};

command.Parameters.Add(_ParamMyObjects);
command.ExecuteNonQuery();

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 for useServerDefault. Passing in true causes the column to not be passed into the INSERT statement if not "set" in the SqlDataRecord. Or, at least that is how I have always gotten IDENTITY columns in UDTTs to work (never tried it on an actual DEFAULT, 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 all sysname which is an alias for NVARCHAR(128). So you really need to at least be using NVARCHAR to avoid potentially data loss / hard-to-trace bug.