How to pass a table-valued parameter to sp_execute_external_script?

Question

I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit input data to the external script:

ALTER procedure [dbo].[testTvp]
(
   @inputTvp dbo.myTvp READONLY
)
AS
BEGIN

   DECLARE @tSqlString nvarchar(100)=N'select * from @inputTvp'

   EXEC sp_execute_external_script
        @language = N'Java',
        @script = N'com.example.TestTvp',
        @input_data_1 = @tSqlString -- this doesn't work

END

When I execute this procedure:

DECLARE @myInput dbo.myTvp

INSERT INTO @myInput values ('a');
INSERT INTO @myInput values ('b');

EXECUTE [dbo].[testTvp] @myInput 

I get the following error:

Must declare the table variable "@inputTvp".

I suppose that means that @inputTvp is not visible (out of scope) to sp_execute_external_script.

Is there a way to pass in @inputTvp to sp_execute_external_script?

I know that I could store the @inputTvp data into a temp table and pass in a t-sql string to query from that temp table. But I'm trying to avoid using a temp table.

Answer 1

Focusing on Table-Valued Parameters (TVPs) is a bit misleading since the question isn't really concerned with them. The example code, in fact, isn't even using the TVP as a TVP, and would be the same question even if presented as asking about table variables. Meaning, the following adaptation of the OP's example code is really the same question since sp_execute_external_script is being passed a string containing a query and not the @inputTvp variable:

DECLARE @inputTableVariable TABLE
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeValue] NVARCHAR(50) NOT NULL
);


DECLARE @tSqlString NVARCHAR(100) = N'SELECT * FROM @inputTableVariable;';


EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @input_data_1 = @tSqlString; -- this doesn't work

Keep in mind that TVPs are meant to send structured data into SQL Server, not out of it. It's also a variable, hence it would only be available to the local context (i.e. not Dynamic SQL or any other sub-process). Still, just to be sure, I tried the following (in SQL Server 2017 so I couldn't specify "Java" as the language):

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputTableVariable dbo.[testTVP] READONLY';

and received:

Msg 39018, Level 16, State 2, Line XXXXX
Parameter '@inputTableVariable' uses a data type that is not supported by the runtime for 'R' script. Unsupported types are timestamp, datetime2, datetimeoffset, time, text, ntext, image, hierarchyid, xml, sql_variant and user-defined type.

---

That being said, you can pass in structured data (avoiding the use of temporary tables), you just need to serialize it as NVARCHAR(MAX) and pass it as a regular parameter (i.e. not an @input_data_1 query). For serialization, you can use either XML or JSON:

Via XML

DECLARE @XmlString NVARCHAR(MAX);

SET @XmlString = (SELECT *
                  FROM   @inputTvp
                  FOR XML AUTO, ROOT('data'));

PRINT @XmlString; -- DEBUG (else comment out)


EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputData NVARCHAR(MAX)',
    @inputData = @XmlString;

Via JSON

DECLARE @JsonString NVARCHAR(MAX);

SET @JsonString = (SELECT *
                   FROM   @inputTvp
                   FOR JSON AUTO, ROOT('data'));

PRINT @JsonString; -- DEBUG (else comment out)

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputData NVARCHAR(MAX)',
    @inputData = @JsonString;

Answer 2

The parameter is documented as a string:

[ @input_data_1 ] Specifies the input data used by the external script in the form of a Transact-SQL query. The data type of input_data_1 is nvarchar(max).

So, I think it's quite simply that it can't be a complex type like a TVP.

I'm not equipped right now to try, but it might be possible to use the other param-related parameters to pass your TVP, assuming your Java program actually knows what to do with it on the other end. I'm guessing this will fail because it won't know anything about dbo.mytvp - that object is in the scope of your database, and can't be mapped to a specific T-SQL data type. But it can't hurt to try when you're stuck:

@params       = N'@tvpInput dbo.mytvp',
@parameter1   = @myInput,
@input_data_1 = @tSqlString

If anyone knows for sure, though, it's either someone at Microsoft, or probably this guy