SQL Server database engine and Azure SQL managed instance don't have built-in functions that would enable you to send information to some API using http protocol. If you would need to call some REST endpoint or a web hook from the T-SQL code, you would need to use WebClient or WebRequest classes from .Net framework and expose them as T-SQL function or procedure. This functionality might enable you implement various scenarios:
In this post you will see how to create that kind of extension.
One of the most popular tool for calling API on http endpoints is curl . CURL can be executed from the command line to send HTTP request to some endpoint. In the following example you can see how to call curl from PowerShell:
$endpoint= "http://......" $header= "aeg-sas-key: ....." $body= "....." curl -X POST -H $header -d "$body" $endpoint
The last command line will call API on specified endpoint with specified header and body.
You can extend T-SQL functionalities by adding CLR procedures and functions that implement CURL-like feature in order to do the same thing in T-SQL.
On the SQL Server GitHub Samples repository, you can find a sample demonstrates how to create CLR User-Defined function/procedure that provides CURL-like funct... . You can download the code and build your .Net assembly that can be imported in SQL Server Database Engine using something like following script:
CREATE ASSEMBLY SqlClrCurl FROM 'C:\GitHub\sql-server-samples\samples\features\sql-clr\Curl\bin\Release\SqlClrCurl.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;
Once you import assembly, you can create a schema and functions that implement CURL functionalities in T-SQL
CREATE SCHEMA CURL; GO --Create the function/procedure CREATE FUNCTION CURL.XGET (@H NVARCHAR(MAX), @url NVARCHAR(4000)) RETURNS NVARCHAR(MAX) AS EXTERNAL NAME SqlClrCurl.Curl.Get; GO CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000)) AS EXTERNAL NAME SqlClrCurl.Curl.Post;
This code will add one function and one procedure in SQL Server that implements the following CURL functions:
Once you create the assembly, you can use CURL functionalities in T-SQL code to call external Web API using Http protocol. The following simple example gets the Microsoft earning from Investors Exchange ("IEX") API data REST API :
select curl.xget(null, 'https://api.iextrading.com/1.0/stock/msft/earnings')
Note that execution time of this procedure depends on the network latency between SQL Server and external API and can be larger that execution of classic database engine procedures.
The following example sends one event to Azure Event Grid .
declare @hkey nvarchar(200) = N'aeg-sas-key: 9CwFFHbPIwTPVEdXS+W7eMnuPk1/+pouIlhzf5='; declare @body nvarchar(4000) = N'[{"id":"1807","eventType":"recordInserted","subject":"myapp/vehicles/motorcycles","eventTime": "2017-08-10T21:03:07+00:00","data": {"make": "Ducati","model":"Monster"},"dataVersion":"1.0","metadataVersion":"1"}]'; declare @endpoint nvarchar(1000) = N'https://test-event-grid.eventgrid.azure.net/api/events'; exec curl.XPOST @H = @hkey, @d = @body, @url = @endpoint;
Note: The code might return an error if your firewall/networking rules don't allow access to targeted Url. Security and configuring access right in your networks is beyond the scope of this sample.
CLR extension in SQL Server Database engine enables you to easily extend T-SQL functionalities and add the features that are not built-in into the SQL Server Database Engine. Accessing external Http endpoints is one of the functionalities that you might add in T-SQL using this sample code from SQL Server GitHub samples repository .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.