0

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

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

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.