How to pass a table-valued parameter to sp_execute_external_script?
1
vote
1
answer
2486
views
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.
Asked by user4321
(215 rep)
Sep 4, 2019, 09:45 PM
Last activity: Oct 14, 2019, 03:02 PM
Last activity: Oct 14, 2019, 03:02 PM