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;