I am trying to create a script in Microsoft SQL Server Management Studio to fetch the current exchange rate from an API and update a database. I am told it is not posisble and common to do this.
-- Change the database context
USE lndb;
-- Declare variables
DECLARE @url NVARCHAR(2000);
DECLARE @responseText NVARCHAR(4000);
DECLARE @exchangeRate DECIMAL(10, 6);
-- Set the API URL
SET @url = 'https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF43718/datos/oportuno?token=734b37b3a5099a9d2d39d06478d47e359a9568cd6693116d95b710e6b8be0008';
-- Make HTTP request and store the response
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @responseText OUTPUT;
EXEC sp_OAMethod @responseText, 'open', NULL, 'GET', @url, false;
EXEC sp_OAMethod @responseText, 'send';
EXEC sp_OAMethod @responseText, 'responseText', @exchangeRate OUTPUT;
EXEC sp_OADestroy @responseText;
-- Convert the response to a decimal
SET @exchangeRate = CONVERT(DECIMAL(10, 6), @exchangeRate);
SELECT @exchangeRate
-- Update the teimcs008401 table in the dbo schema
INSERT INTO dbo.teimcs008401 (t_bcur, t_ccur, t_rate, t_stdt, t_Refcntd, t_Refcntu)
VALUES ('MXP', 'USD', @exchangeRate, GETDATE(), '0', '0');
-- Display the updated exchange rate
SELECT * FROM dbo.teimcs008401;
