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
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)

-- user defined table type transport mechanism
    ServerName varchar(200)

-- stored procedure to reproduce issue
    @MetricData dbo.UDTT READONLY

    INSERT INTO dbo.UDTT_holder 
    SELECT MD.* FROM @MetricData MD

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.

    @p3 dbo.UDTT
,   @MetricData dbo.UDTT

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


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

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

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

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.


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
  • 1
    +1 Currently the script in the OP just has the statement dbo.Repro and doesn't use the passed in parameters at all. – Martin Smith Feb 3 '12 at 11:14
  • Yesssss, I saw Rob's comment and while it didn't need the EXEC portion in there, it did make sense that the reason the parameter wasn't being used in with the call was the parameter was not referenced in the script. Updating ticket to reflect my foolishness – billinkc Feb 3 '12 at 15:12
  • @billinkc - Ah I just added an answer with more detailed explanation then saw your comment. I'll delete that answer in a bit then. – Martin Smith Feb 3 '12 at 15:16
  • Right. You hadn't mentioned you were doing this from ado.net. sp_executesql is equivalent to a .CommandText, not .StoredProcedure. – Rob Farley Feb 3 '12 at 21:23

I was tempted to delete this question but figured someone else might run into a similar circumstance.

The root cause was my $updateCommand had not set the CommandType. The default value is Text so my stored procedure was being called correctly. My parameters were being created and passed but as noted in the other answers just because the parameters are available does not mean they are being used.

Code in case anyone was interested in my mistake

    $updateConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\localsqla;Initial Catalog=baseline;Integrated Security=SSPI;")

    $updateCommand = New-Object System.Data.SqlClient.SqlCommand($updateQuery)
    $updateCommand.Connection = $updateConnection

    # This is what I was missing
    $updateCommand.CommandType = [System.Data.CommandType]::StoredProcedure
    #$updateCommand.CommandType = [System.Data.CommandType]::Text
    #$updateCommand.CommandType = [System.Data.CommandType]::TableDirect

    $DataTransferFormat = $updateCommand.Parameters.AddWithValue("@MetricData", $dataTable)
    $DataTransferFormat.SqlDbType = [System.Data.SqlDbType]::Structured
    $DataTransferFormat.TypeName = $udtt
    $results = $updateCommand.ExecuteNonQuery()

Running with a value of CommandType of Text would require @rob_farley's solution of changing the value of $updateQuery to "EXEC dbo.Repro @MetricData". At that point, the sp_executesql will map values correctly and life is good.

Running with a CommandType of TableDirect is not supported by the SqlClient Data Provider, as the documentation indicates.

Using a CommandType of StoredProcedure translates to a direct invocation of the stored procedure without the sp_executesql wrapper and works great.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.