12

Problem

Is there a known issue with user defined table types as parameters to sp_executesql?

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.

11

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

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

Resolution notes

Rob and Martin Smith (Deleted answer 10k+) 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.

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;")
    $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.

0

I had a similar problem, and the accepted answer above ALMOST worked. However, I needed to fully qualify with the database name. I needed something like this:

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

In my case, I wasn't using 'dbo', so it may be only necessary in that case.

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.