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;