Sample Header Ad - 728x90

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