Passing a variable in OPENJSON WITH statement in SQL Server

Question

Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason statement to read value of FX(last statement of below script). I am not able to pass this as a parameter to make SampleValue dynamic. I tried using dynamic SQL but did not succeed. Please help.

/*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO
*/
DECLARE @Object Int
 , @hr int
 , @Base char(3)
 , @Symbol char(3)
 , @Url varchar(1000)
 ,@Param varchar(1000)
DECLARE @json as table(Json_Table nvarchar(max))
SET @Base = 'USD'
SET @Symbol ='GBP'
SET @Url = 'https://api.ratesapi.io/api/latest?base=' + @Base + '&symbols=' + @Symbol ;--Your Web Service Url (invoked)
SET @Param = '$.rates.' + @Symbol;
select @Url; --https://api.ratesapi.io/api/latest?base=USD&symbols=GBP
Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL,'get', @Url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
SELECT *
FROM OPENJSON((select Json_table from @json), N'$')
WITH ( 
 SampleValue nvarchar(max) '$.rates.GBP' , 
 SampleDate DATE '$.date'
 ) ;
 /*DECLARE @SQL NVARCHAR(MAX) = 
 'SELECT * FROM OPENJSON((select Json_table from @json), N''$'') WITH ( SampleValue nvarchar(max) ''$.rates.GBP'', SampleDate DATE ''$.date'' ) ;'
 select @SQL; 
 EXEC sp_executesql @SQL;
 DECLARE @SQLstring NVARCHAR(MAX) = 
'SELECT *
FROM OPENJSON((select Json_table from @json), N''$'') WITH ( 
 SampleValue nvarchar(max) '+ ''''+ @param + ''''+ ' , 
 SampleDate DATE' + ''''+ '$.date' + '''' + ' ) ;'
 select @SQLstring ; 
EXEC sp_executesql @SQLstring ;*/
EXEC sp_OADestroy @Object

Answer 1

It looks like there were two issues with your dynamic SQL approach:

1. You didn't properly escape the single quotes in the @Param variable
2. You can't access a table variable inside the scope of a dynamic SQL statement
   • For details on this, and workarounds, see Erik Darling's article Starting SQL: Dynamic SQL And Temporary Objects

With those things addressed, here's a slimmed down version of the code that demonstrates how the dynamic SQL can work, using a temporary table instead of a table variable.

CREATE TABLE #json
(
 Json_Table nvarchar(max)
);
DECLARE @Param varchar(1000),
 @Symbol char(3),
 @Sql nvarchar(max);
SET @Symbol ='GBP';
SET @Param = '$.rates.' + @Symbol;
SET @Sql = N'
SELECT *
FROM OPENJSON((SELECT Json_Table FROM #json), N''$'')
WITH 
(
 SampleValue nvarchar(max) ''' + @Param + ''',
 SampleDate date ''$.date''
);
';
INSERT INTO #json 
 (Json_Table)
SELECT 
 N'{"base":"USD","rates":{"GBP":0.7739357155},"date":"2020年10月15日"}';
EXEC sys.sp_executesql @SQL;

Screenshot of query results showing SampleValue and SampleDate correctly populated

See an interactive example of this at the db<>fiddle link.