Sql-server – Passing a variable in OPENJSON WITH statement in SQL Server

dynamic-sqljsonsql serversql-server-2016sql-variant-property

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

Best Answer

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

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.

Related Question