Hey guys!
In today's post, I will share with you a solution that I developed to query the exchange rate of the dollar (USD), euro (EUR), real (BRL) or any other currency in real time (almost) using a WebService (API) and SQLCLR in SQL Server. This can end up being very useful depending on your area of ​​activity and it is something that I see many companies even purchasing services similar to this.

If you don't know what SQLCLR is, read the article Introduction to SQL CLR (Common Language Runtime) in SQL Server before this post to better understand what this feature is.

What we will be able to do at the end of this post:

Where does quote data come from?

To return currency quote data, there are several websites and APIs to consult. For this example, I chose to use the website's API https://www.currencyconverterapi.com/, which has a free consultation plan, as well as paid plans, if you have a greater need for consultations.

API documentation can be found at this link here.

On the plane free, I believe that most people will already be very well served. In fact, on the website itself it is mentioned that the service can be used free of charge even for commercial use.

Free plan limitations:

  • Currency pairs per request: 2 (You can check up to 2 currencies at a time)
  • Number of requests per hour: 100
  • Quote data update interval: 60 minutes (In other words, there is no point in trying to update the data on the free plan less frequently than every 60 minutes)
  • Date range in historical search: 8 days (The fncConverteMoedaHistoricoRange function can only return a data range of up to 8 days)
  • Historical queries: 1 year (The dates passed by parameters in the fncConverteMoedaHistorico or fncConverteMoedaHistoricoRange functions cannot be older than 365 days from the current date)

If the free plan is unable to serve you, you can look for a paid plan and continue using this same API and these functions.

How to get the API authentication key?

To purchase your API key and start using the features, access this link here, fill in your email and you will receive your access key:

With this key, you can now use your API, respecting the limits of the plan you are using, of course.

How to use the created functions

Before demonstrating their use, I thought it would be nice to give a brief description of each of these functions.

  • fncConverteCurrency: Function that aims to convert one currency to another, based on the most current exchange rate possible. The return of this function is a scalar decimal number.
  • fncConvertCurrencyHistory: Function that aims to convert one currency to another, based on the date quote entered as a parameter (bearing in mind the limitations of the API). The return of this function is a scalar decimal number.
  • fncConvertCurrencyHistoryRange: Function that aims to convert one currency to another, based on the date range quote entered as a parameter (remembering the API limitations). The return of this function is a table, containing the quote date (Datetime) and the quote value (scalar decimal number), where each day is a line in the table.

Usage examples:

DECLARE @tokenApi VARCHAR(50) = 'Meu token'


-- Retorna a cotação mais atual da moeda desejada
SELECT dbo.fncConverteMoeda(@tokenApi, 'USD', 'BRL')


-- Retorna a cotação da moeda na data desejada
SELECT dbo.fncConverteMoedaHistorico(@tokenApi, 'USD', 'BRL', '2020-07-30')


-- Retorna uma tabela com os rates no intervalo desejado
SELECT * FROM dbo.fncConverteMoedaHistoricoRange(@tokenApi, 'USD', 'BRL', '2021-01-01', '2021-01-08')

Result:

How to create objects in the database with T-SQL – Easy/Nutella Mode

If you don't have Visual Studio or don't want to worry about understanding how the functions were built or compiling your own version of the assembly, this code is for you. Simple, fast and objective.

T-SQL code to create the functions:

USE [master]
GO

DECLARE 
    @VersaoSQL INT = CONVERT(INT, (SERVERPROPERTY('ProductMajorVersion')))


IF (@VersaoSQL >= 15) -- SQL Server 2017+
BEGIN
    
    EXEC ('
    DECLARE @asmBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103000875C2600000000000000000E00022200B01300000120000000600000000000036310000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000E43000004F00000000400000E802000000000000000000000000000000000000006000000C000000AC2F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000003C110000002000000012000000020000000000000000000000000000200000602E72737263000000E8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000183100000000000048000000020005008C250000200A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400F600000001000011001C8D1000000125167201000070A2251703A225187259000070A2251904A2251A725D000070A2251B02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A74090000010D00096F0A00000A130400110414FE03130511052C2E00110472B7000070280B00000A730C00000A13060011066F0D00000A0C00DE0D11062C0811066F0E00000A00DC0000DE0D11042C0811046F0E00000A00DC00DE0B092C07096F0E00000A00DC080872C30000706F0F00000A17586F1000000A72C700007072B50000706F1100000A281200000A13072B0011072A000001280000020090000C9C000D00000000020070003DAD000D000000000200670056BD000B000000001B3005001901000002000011001E8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A0D120372F5000070281400000AA2251C720B010070A2251D02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A740900000113040011046F0A00000A130500110514FE03130611062C2E00110572B7000070280B00000A730C00000A13070011076F0D00000A0C00DE0D11072C0811076F0E00000A00DC0000DE0D11052C0811056F0E00000A00DC00DE0D11042C0811046F0E00000A00DC080872C30000706F1500000A17586F1000000A721D01007072B50000706F1100000A281200000A13082B0011082A000000012800000200B1000CBD000D00000000020091003DCE000D000000000200870057DE000D000000001B300500170200000300001100731600000A0A1F0A8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A1304120472F5000070281400000AA2251C7223010070A2251D0F04281300000A1304120472F5000070281400000AA2251E720B010070A2251F0902A2280500000A0B07280600000A74080000010C08728B0000706F0700000A000872930000706F0800000A0072B50000700D086F0900000A740900000113050011056F0A00000A130600110614FE0313071107392601000000110672B7000070280B00000A730C00000A13080011086F0D00000A0D097237010070196F1700000A16FE0416FE01130B110B2C2800067E1800000A23000000000000F0BF281900000A73060000066F1A00000A2606130CDDEE00000009724301007072B50000706F1100000A72C700007072B50000706F1100000A724701007072B50000706F1100000A0D090972C30000706F0F00000A17586F1000000A0D09178D1700000125161F2C9D6F1B00000A130911098E69130A16130D2B4B001109110D9A130E110E178D1700000125161F3A9D6F1B00000A130F06110F169A281C00000A281D00000A110F179A281E00000A281900000A73060000066F1A00000A2600110D1758130D110D110AFE04131011102DA900DE0D11082C0811086F0E00000A00DC0000DE0D11062C0811066F0E00000A00DC00DE0D11052C0811056F0E00000A00DC06130C2B00110C2A00414C000002000000DD00000004010000E10100000D0000000000000002000000BA00000038010000F20100000D0000000000000002000000B000000052010000020200000D00000000000000133002002100000004000011000274030000020A03067B01000004810C00000104067B0200000481070000012A2202281F00000A002A5E02281F00000A000002037D0100000402047D020000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000010030000237E00007C0300009803000023537472696E677300000000140700004C010000235553006008000010000000234755494400000070080000B001000023426C6F620000000000000002000001571502000902000000FA0133001600000100000018000000030000000200000006000000110000001F000000060000000400000001000000030000000100000000009E0101000000000006002B01D30206004B01D30206000201C0020F00F302000006004603DE010A0016019F020A00970002030E0072034D030E00D1004D030600CB010A0006006E020A000A00AA0002030600B800DE0106007F00170306008103170306008D01DE010E0076034D030E00D5004D03060082018B0306007B020A0006008B00DE010600F301DE0106006902DE0106006A03DE010000000001000000000001000100010010002A030000150001000100030010001F02000015000100060006002C00A30006002702D90050200000000096001400DD0001007C210000000096003702E5000400CC220000000096006000EF0008003C250000000094000402FB000D006925000000008618BA02060010007225000000008618BA020601100000000100ED0100000200D201000003005C0200000100ED0100000200D201000003005C0200000400310000000100ED0100000200D201000003005C0200000400510200000500E501000001001C02020002003100020003002F02000001003100000002002F020900BA0201001100BA0206001900BA020A003100BA02060081003F0320008900FB00260089004D002C008900C1002C008900E10031009100C001360099007F013B005900BA024100A10043004900A900ED000600810077014D008100940152008100580057003900F5005D0061006901750069008B017A00810073014D007900BA020600810077019C006100BB01A30039005803A70079003F00AD0081006403B200C100B600B90061005803BF00C100A100C6002900BA020600200023003F012E000B000E012E00130017012E001B003601400023003F01600023004401100063007F00CB0004800000000000000000000000000000000086020000020000000000000000000000D000360000000000020000000000000000000000D000250000000000020000000000000000000000D000DE0100000000030002000000003C4D6F64756C653E0053797374656D2E494F00666E63436F6E76657274654D6F6564610053797374656D2E446174610064617461006D73636F726C6962004164640052656164546F456E64007365745F4D6574686F64005265706C61636500666E63436F6E76657274654D6F656461486973746F7269636F52616E67650049456E756D657261626C650049446973706F7361626C650053716C446F75626C6500546F446F75626C650053716C4461746554696D6500546F4461746554696D65007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500446973706F7365005061727365004372656174650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565004C617374496E6465784F6600476574456E636F64696E6700546F537472696E6700537562737472696E670053514C434C525F43757272656E6379436F6E7665727465722E646C6C004E756C6C00476574526573706F6E736553747265616D006D6F6564614F726967656D0053797374656D006461746146696D00746F6B656E00537472696E67436F6D70617269736F6E0046696C6C526F775F436F6E73756C7461436F746163616F006F626A436F6E73756C7461436F746163616F00636F746163616F00666E63436F6E76657274654D6F656461486973746F7269636F0064617461496E6963696F006D6F65646144657374696E6F00436861720053747265616D52656164657200546578745265616465720053514C434C525F43757272656E6379436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E636174004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C697400436F6E766572740048747470576562526571756573740041727261794C6973740053797374656D2E5465787400000057680074007400700073003A002F002F0066007200650065002E00630075007200720063006F006E0076002E0063006F006D002F006100700069002F00760037002F0063006F006E0076006500720074003F0071003D0000035F00002D260063006F006D0070006100630074003D0075006C0074007200610026006100700069004B00650079003D00000747004500540000216100700070006C00690063006100740069006F006E002F006A0073006F006E000001000B5500540046002D00380001033A0000037D000029260063006F006D0070006100630074003D0075006C00740072006100260064006100740065003D00001579007900790079002D004D004D002D0064006400011126006100700069004B00650079003D0000057D007D000013260065006E00640044006100740065003D00000B6500720072006F00720000037B00000322000000815DB5CD93F7514E893DE6B90EF8AE0B000420010108032000010520010111110F07080E12210E1225122902122D111D0500010E1D0E05000112450E042001010E04200012490420001229050001124D0E072002011229124D0320000E042001080E0420010E080520020E0E0E050001111D0E1107090E12210E11351225122902122D111D04200011350420010E0E1C0711123D0E12210E11351225122902122D1D0E08021239080E1D0E02062002080E115903061131050001111D0D042001081C0620011D0E1D0305000111350E060001113111350400010D0E040701120C08B77A5C561934E0890306111D070003111D0E0E0E090004111D0E0E0E11310B000512390E0E0E113111310A0003011C10113110111D072002011131111D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010007010000000004010000006801000200540E1146696C6C526F774D6574686F644E616D651746696C6C526F775F436F6E73756C7461436F746163616F540E0F5461626C65446566696E6974696F6E2544745F436F746163616F204441544554494D452C20566C5F436F746163616F20464C4F4154000000000000000875C26000000000020000001C010000C82F0000C811000052534453867155FADDC9CB49A9C76F5E5FCA004701000000433A5C55736572735C646966696C5C736F757263655C7265706F735C53514C434C525F43757272656E6379436F6E7665727465725C53514C434C525F43757272656E6379436F6E7665727465725C6F626A5C44656275675C53514C434C525F43757272656E6379436F6E7665727465722E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C31000000000000000000002631000000200000000000000000000000000000000000000000000018310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000008C02000000000000000000008C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004EC010000010053007400720069006E006700460069006C00650049006E0066006F000000C801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000005A001D00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000062001D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000383100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    DECLARE @hash varbinary(64) = HASHBYTES(''SHA2_512'', @asmBin);

    IF (EXISTS(SELECT TOP(1) NULL FROM sys.trusted_assemblies WHERE [hash] = @hash))
    BEGIN
        EXEC sys.sp_drop_trusted_assembly @hash = @hash
    END


    EXEC sys.sp_add_trusted_assembly 
        @hash = @hash,
        @description = N''sqlclr_currencyconverter, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil'''
    )

END
ELSE BEGIN
    
    EXEC('ALTER DATABASE [dirceuresende] SET TRUSTWORTHY ON')

END


USE [dirceuresende]
GO

IF (EXISTS(SELECT TOP(1) NULL FROM sys.assemblies WHERE [name] = 'SQLCLR_CurrencyConverter'))
BEGIN

    IF (OBJECT_ID('dbo.fncConverteMoeda') IS NOT NULL) DROP FUNCTION dbo.fncConverteMoeda
    IF (OBJECT_ID('dbo.fncConverteMoedaHistorico') IS NOT NULL) DROP FUNCTION dbo.fncConverteMoedaHistorico
    IF (OBJECT_ID('dbo.fncConverteMoedaHistoricoRange') IS NOT NULL) DROP FUNCTION dbo.fncConverteMoedaHistoricoRange

    DROP ASSEMBLY [SQLCLR_CurrencyConverter]

END


CREATE ASSEMBLY [SQLCLR_CurrencyConverter]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103000875C2600000000000000000E00022200B01300000120000000600000000000036310000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000E43000004F00000000400000E802000000000000000000000000000000000000006000000C000000AC2F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000003C110000002000000012000000020000000000000000000000000000200000602E72737263000000E8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000183100000000000048000000020005008C250000200A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400F600000001000011001C8D1000000125167201000070A2251703A225187259000070A2251904A2251A725D000070A2251B02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A74090000010D00096F0A00000A130400110414FE03130511052C2E00110472B7000070280B00000A730C00000A13060011066F0D00000A0C00DE0D11062C0811066F0E00000A00DC0000DE0D11042C0811046F0E00000A00DC00DE0B092C07096F0E00000A00DC080872C30000706F0F00000A17586F1000000A72C700007072B50000706F1100000A281200000A13072B0011072A000001280000020090000C9C000D00000000020070003DAD000D000000000200670056BD000B000000001B3005001901000002000011001E8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A0D120372F5000070281400000AA2251C720B010070A2251D02A2280500000A0A06280600000A74080000010B07728B0000706F0700000A000772930000706F0800000A0072B50000700C076F0900000A740900000113040011046F0A00000A130500110514FE03130611062C2E00110572B7000070280B00000A730C00000A13070011076F0D00000A0C00DE0D11072C0811076F0E00000A00DC0000DE0D11052C0811056F0E00000A00DC00DE0D11042C0811046F0E00000A00DC080872C30000706F1500000A17586F1000000A721D01007072B50000706F1100000A281200000A13082B0011082A000000012800000200B1000CBD000D00000000020091003DCE000D000000000200870057DE000D000000001B300500170200000300001100731600000A0A1F0A8D1000000125167201000070A2251703A225187259000070A2251904A2251A72CB000070A2251B0F03281300000A1304120472F5000070281400000AA2251C7223010070A2251D0F04281300000A1304120472F5000070281400000AA2251E720B010070A2251F0902A2280500000A0B07280600000A74080000010C08728B0000706F0700000A000872930000706F0800000A0072B50000700D086F0900000A740900000113050011056F0A00000A130600110614FE0313071107392601000000110672B7000070280B00000A730C00000A13080011086F0D00000A0D097237010070196F1700000A16FE0416FE01130B110B2C2800067E1800000A23000000000000F0BF281900000A73060000066F1A00000A2606130CDDEE00000009724301007072B50000706F1100000A72C700007072B50000706F1100000A724701007072B50000706F1100000A0D090972C30000706F0F00000A17586F1000000A0D09178D1700000125161F2C9D6F1B00000A130911098E69130A16130D2B4B001109110D9A130E110E178D1700000125161F3A9D6F1B00000A130F06110F169A281C00000A281D00000A110F179A281E00000A281900000A73060000066F1A00000A2600110D1758130D110D110AFE04131011102DA900DE0D11082C0811086F0E00000A00DC0000DE0D11062C0811066F0E00000A00DC00DE0D11052C0811056F0E00000A00DC06130C2B00110C2A00414C000002000000DD00000004010000E10100000D0000000000000002000000BA00000038010000F20100000D0000000000000002000000B000000052010000020200000D00000000000000133002002100000004000011000274030000020A03067B01000004810C00000104067B0200000481070000012A2202281F00000A002A5E02281F00000A000002037D0100000402047D020000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000010030000237E00007C0300009803000023537472696E677300000000140700004C010000235553006008000010000000234755494400000070080000B001000023426C6F620000000000000002000001571502000902000000FA0133001600000100000018000000030000000200000006000000110000001F000000060000000400000001000000030000000100000000009E0101000000000006002B01D30206004B01D30206000201C0020F00F302000006004603DE010A0016019F020A00970002030E0072034D030E00D1004D030600CB010A0006006E020A000A00AA0002030600B800DE0106007F00170306008103170306008D01DE010E0076034D030E00D5004D03060082018B0306007B020A0006008B00DE010600F301DE0106006902DE0106006A03DE010000000001000000000001000100010010002A030000150001000100030010001F02000015000100060006002C00A30006002702D90050200000000096001400DD0001007C210000000096003702E5000400CC220000000096006000EF0008003C250000000094000402FB000D006925000000008618BA02060010007225000000008618BA020601100000000100ED0100000200D201000003005C0200000100ED0100000200D201000003005C0200000400310000000100ED0100000200D201000003005C0200000400510200000500E501000001001C02020002003100020003002F02000001003100000002002F020900BA0201001100BA0206001900BA020A003100BA02060081003F0320008900FB00260089004D002C008900C1002C008900E10031009100C001360099007F013B005900BA024100A10043004900A900ED000600810077014D008100940152008100580057003900F5005D0061006901750069008B017A00810073014D007900BA020600810077019C006100BB01A30039005803A70079003F00AD0081006403B200C100B600B90061005803BF00C100A100C6002900BA020600200023003F012E000B000E012E00130017012E001B003601400023003F01600023004401100063007F00CB0004800000000000000000000000000000000086020000020000000000000000000000D000360000000000020000000000000000000000D000250000000000020000000000000000000000D000DE0100000000030002000000003C4D6F64756C653E0053797374656D2E494F00666E63436F6E76657274654D6F6564610053797374656D2E446174610064617461006D73636F726C6962004164640052656164546F456E64007365745F4D6574686F64005265706C61636500666E63436F6E76657274654D6F656461486973746F7269636F52616E67650049456E756D657261626C650049446973706F7361626C650053716C446F75626C6500546F446F75626C650053716C4461746554696D6500546F4461746554696D65007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500446973706F7365005061727365004372656174650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565004C617374496E6465784F6600476574456E636F64696E6700546F537472696E6700537562737472696E670053514C434C525F43757272656E6379436F6E7665727465722E646C6C004E756C6C00476574526573706F6E736553747265616D006D6F6564614F726967656D0053797374656D006461746146696D00746F6B656E00537472696E67436F6D70617269736F6E0046696C6C526F775F436F6E73756C7461436F746163616F006F626A436F6E73756C7461436F746163616F00636F746163616F00666E63436F6E76657274654D6F656461486973746F7269636F0064617461496E6963696F006D6F65646144657374696E6F00436861720053747265616D52656164657200546578745265616465720053514C434C525F43757272656E6379436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E636174004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C697400436F6E766572740048747470576562526571756573740041727261794C6973740053797374656D2E5465787400000057680074007400700073003A002F002F0066007200650065002E00630075007200720063006F006E0076002E0063006F006D002F006100700069002F00760037002F0063006F006E0076006500720074003F0071003D0000035F00002D260063006F006D0070006100630074003D0075006C0074007200610026006100700069004B00650079003D00000747004500540000216100700070006C00690063006100740069006F006E002F006A0073006F006E000001000B5500540046002D00380001033A0000037D000029260063006F006D0070006100630074003D0075006C00740072006100260064006100740065003D00001579007900790079002D004D004D002D0064006400011126006100700069004B00650079003D0000057D007D000013260065006E00640044006100740065003D00000B6500720072006F00720000037B00000322000000815DB5CD93F7514E893DE6B90EF8AE0B000420010108032000010520010111110F07080E12210E1225122902122D111D0500010E1D0E05000112450E042001010E04200012490420001229050001124D0E072002011229124D0320000E042001080E0420010E080520020E0E0E050001111D0E1107090E12210E11351225122902122D111D04200011350420010E0E1C0711123D0E12210E11351225122902122D1D0E08021239080E1D0E02062002080E115903061131050001111D0D042001081C0620011D0E1D0305000111350E060001113111350400010D0E040701120C08B77A5C561934E0890306111D070003111D0E0E0E090004111D0E0E0E11310B000512390E0E0E113111310A0003011C10113110111D072002011131111D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010007010000000004010000006801000200540E1146696C6C526F774D6574686F644E616D651746696C6C526F775F436F6E73756C7461436F746163616F540E0F5461626C65446566696E6974696F6E2544745F436F746163616F204441544554494D452C20566C5F436F746163616F20464C4F4154000000000000000875C26000000000020000001C010000C82F0000C811000052534453867155FADDC9CB49A9C76F5E5FCA004701000000433A5C55736572735C646966696C5C736F757263655C7265706F735C53514C434C525F43757272656E6379436F6E7665727465725C53514C434C525F43757272656E6379436F6E7665727465725C6F626A5C44656275675C53514C434C525F43757272656E6379436F6E7665727465722E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C31000000000000000000002631000000200000000000000000000000000000000000000000000018310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000008C02000000000000000000008C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004EC010000010053007400720069006E006700460069006C00650049006E0066006F000000C801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000005A001D00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000062001D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F00430075007200720065006E006300790043006F006E007600650072007400650072002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000383100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE FUNCTION [dbo].[fncConverteMoeda] (@token NVARCHAR (MAX), @moedaOrigem NVARCHAR (MAX), @moedaDestino NVARCHAR (MAX) )
RETURNS FLOAT
AS
EXTERNAL NAME [SQLCLR_CurrencyConverter].[UserDefinedFunctions].[fncConverteMoeda]
GO

CREATE FUNCTION [dbo].[fncConverteMoedaHistorico] ( @token NVARCHAR (MAX), @moedaOrigem NVARCHAR (MAX), @moedaDestino NVARCHAR (MAX), @data DATETIME )
RETURNS FLOAT
AS
EXTERNAL NAME [SQLCLR_CurrencyConverter].[UserDefinedFunctions].[fncConverteMoedaHistorico]
GO

CREATE FUNCTION [dbo].[fncConverteMoedaHistoricoRange] ( @token NVARCHAR (MAX), @moedaOrigem NVARCHAR (MAX), @moedaDestino NVARCHAR (MAX), @dataInicio DATETIME, @dataFim DATETIME )
RETURNS TABLE (
    [Dt_Cotacao] DATETIME   NULL,
    [Vl_Cotacao] FLOAT      NULL
)
AS
EXTERNAL NAME [SQLCLR_CurrencyConverter].[UserDefinedFunctions].[fncConverteMoedaHistoricoRange]
GO

Be very careful when using the TRUSTWORTHY parameter in the database, as in a given situation, it can be a serious security risk to your environment, as I demonstrated in the article SQL Server – Understanding the risks of the TRUSTWORTHY property enabled in a database. Try to use the SQL Server 2017+ trusted assembly feature or asymmetric key, if possible.

How to compile your SQLCLR project in Visual Studio – Hard/Root Mode

If you have Visual Studio and want to understand how the functions were built or compile your own version of the assembly, this code is for you.

If you don't know what SQLCLR is or how to compile and publish the project, read the article Introduction to SQL CLR (Common Language Runtime) in SQL Server before this post to better understand what this feature is.

Visual Studio Solution Download (VS2019)
SQLCLR_CurrencyConverter Project Download

fncConverteMoeda.cs source code

using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble fncConverteMoeda (string token, string moedaOrigem, string moedaDestino) 
    {

        var url = "https://free.currconv.com/api/v7/convert?q=" + moedaOrigem + "_" + moedaDestino + "&compact=ultra&apiKey=" + token;
        var request = (HttpWebRequest)WebRequest.Create(url);

        request.Method = "GET";
        request.ContentType = "application/json";
        var resposta = "";

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                    {
                        resposta = streamReader.ReadToEnd();
                    }
                }
            }
        }

        return SqlDouble.Parse(resposta.Substring(resposta.IndexOf(":") + 1).Replace("}", "")) ;

    }

}

fncConverteMoedaHistorico.cs source code

using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble fncConverteMoedaHistorico(string token, string moedaOrigem, string moedaDestino, SqlDateTime data)
    {

        var url = "https://free.currconv.com/api/v7/convert?q=" + moedaOrigem + "_" + moedaDestino + "&compact=ultra&date=" + data.Value.ToString("yyyy-MM-dd") + "&apiKey=" + token;
        var request = (HttpWebRequest)WebRequest.Create(url);

        request.Method = "GET";
        request.ContentType = "application/json";
        var resposta = "";

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                    {
                        resposta = streamReader.ReadToEnd();
                    }
                }
            }
        }

        return SqlDouble.Parse(resposta.Substring(resposta.LastIndexOf(":") + 1).Replace("}}", ""));

    }

}

fncConverteMoedaHistoricoRange.cs source code

using System;
using System.Collections;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;

public partial class UserDefinedFunctions
{

    private class ConsultaCotacao
    {

        public SqlDateTime Data;
        public SqlDouble Cotacao;
        
        public ConsultaCotacao(SqlDateTime data, SqlDouble cotacao)
        {

            Data = data;
            Cotacao = cotacao;
            
        }

    }


    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "FillRow_ConsultaCotacao",
        TableDefinition = "Dt_Cotacao DATETIME, Vl_Cotacao FLOAT"
    )]
    public static IEnumerable fncConverteMoedaHistoricoRange(string token, string moedaOrigem, string moedaDestino, SqlDateTime dataInicio, SqlDateTime dataFim)
    {

        var consultaCotacaoCollection = new ArrayList();

        var url = "https://free.currconv.com/api/v7/convert?q=" + moedaOrigem + "_" + moedaDestino + "&compact=ultra&date=" + dataInicio.Value.ToString("yyyy-MM-dd") + "&endDate=" + dataFim.Value.ToString("yyyy-MM-dd") + "&apiKey=" + token;
        var request = (HttpWebRequest)WebRequest.Create(url);

        request.Method = "GET";
        request.ContentType = "application/json";
        var resposta = "";

        using (var response = (HttpWebResponse)request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding("UTF-8")))
                    {

                        resposta = streamReader.ReadToEnd();

                        if (resposta.IndexOf("error", StringComparison.InvariantCultureIgnoreCase) >= 0)
                        {

                            consultaCotacaoCollection.Add(new ConsultaCotacao(
                                SqlDateTime.Null,
                                -1
                            ));

                            return consultaCotacaoCollection;

                        }

                        resposta = resposta.Replace("{", "").Replace("}", "").Replace("\"", "");
                        resposta = resposta.Substring(resposta.IndexOf(":") + 1);

                        var varLinhas = resposta.Split(',');
                        var numLinhas = varLinhas.Length;

                        for(var i = 0; i < numLinhas; i++)
                        {

                            var linha = varLinhas[i];
                            var palavras = linha.Split(':');

                            consultaCotacaoCollection.Add(new ConsultaCotacao(
                                Convert.ToDateTime(palavras[0]),
                                Convert.ToDouble(palavras[1])
                            ));

                        }

                    }
                }
            }
        }

        return consultaCotacaoCollection;

    }


    protected static void FillRow_ConsultaCotacao(object objConsultaCotacao, out SqlDateTime data, out SqlDouble cotacao)
    {

        var consultaCotacao = (ConsultaCotacao) objConsultaCotacao;

        data = consultaCotacao.Data;
        cotacao = consultaCotacao.Cotacao;

    }

}

And that's it, folks!
I hope you liked this tip and see you next time!