Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

-- Proc 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 proc itself and finally dump the audit table.

As you can see from the timestamp, the record for the B record corresponds to the straight proc 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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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
share|improve this answer
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 proc 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;")
    $updateConnection.Open()

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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