Call API End Point from SQL Server

Follow following links to know more about different type of databases and SQL Server Concepts.

• Choosing Right Database for Application
• Database Interview Question and Answers

Pre-Requisites

Enable following advance options in Microsoft SQL Server by running following SQL statement in SSMS. These option are must to Call API End Point from SQL Server.

sp_configure 'show advanced options', 1;
go
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

SQL Script to Call API End Point from SQL Server

Following script Prepare and Send API request. Request URL, Headers and Body are defined using SQL parameters.

-----------------------
--Variables
-----------------------
DECLARE
--Add API end Point URL here
@Url varchar(8000) = ''

--Define Request Type POST,GET
,@Method varchar(5) = 'POST'

--normally json object string : '{"key":"value"}'
,@BodyData nvarchar(max) = '{"InventoryType": "Delivery","RequestedDate": "09/02/2021","StoreId": ""}'

--Basic auth token, Api key
,@Authorization varchar(8000) = NULL

--'application/xml'
,@ContentType varchar(255) = 'application/json'		

--token of WinHttp object
,@WinTokken int 
,@ReturnCode int; 

Declare @Response TABLE (ResponseText nvarchar(max));


-----------------------
--Create Token
-----------------------
--Creates an instance of WinHttp.WinHttpRequest
EXEC @ReturnCode = sp_OACreate 'WinHttp.WinHttpRequest.5.1',@WinTokken OUT
IF @ReturnCode <> 0 GOTO EXCEPTION

--Opens an HTTP connection to an HTTP resource.
EXEC @ReturnCode = sp_OAMethod @WinTokken, 'Open', NULL, @Method/*Method*/, @Url /*Url*/, 'false' /*IsAsync*/
IF @ReturnCode <> 0 GOTO EXCEPTION

-----------------------
--Create Headers
-----------------------
--Create Request Headers. As of now this request include Authorization and Content-Type in headers.
IF @Authorization IS NOT NULL
BEGIN
	EXEC @ReturnCode = sp_OAMethod @WinTokken, 'SetRequestHeader', NULL, 'Authorization', @Authorization
	IF @ReturnCode <> 0 GOTO EXCEPTION
END

IF @ContentType IS NOT NULL
BEGIN
	EXEC @ReturnCode = sp_OAMethod @WinTokken, 'SetRequestHeader', NULL, 'Content-Type', @ContentType
	IF @ReturnCode <> 0 GOTO EXCEPTION
END

-- New Header can be added like below commented code
--IF @OUN IS NOT NULL
--BEGIN
--	EXEC @ReturnCode = sp_OAMethod @WinTokken, 'SetRequestHeader', NULL, 'OUN', @OUN
--	IF @ReturnCode <> 0 GOTO EXCEPTION
--END

-----------------------
--Send Request
-----------------------
--Sends an HTTP request to an HTTP server. Following Code Defines Request Body
IF @BodyData IS NOT NULL
BEGIN
	EXEC @ReturnCode = sp_OAMethod @WinTokken,'Send', NULL, @BodyData
	IF @ReturnCode <> 0 GOTO EXCEPTION
END
ELSE
BEGIN
	EXEC @ReturnCode = sp_OAMethod @WinTokken,'Send'
	IF @ReturnCode <> 0 GOTO EXCEPTION
END

IF @ReturnCode <> 0 GOTO EXCEPTION

-----------------------
--Get Response
-----------------------
--Get Response text
INSERT INTO @Response (ResponseText) 
EXEC @ReturnCode = sp_OAGetProperty @WinTokken,'ResponseText'

IF @ReturnCode <> 0 GOTO EXCEPTION
IF @ReturnCode = 0 GOTO RESULT

-----------------------
--Exception Block
-----------------------
EXCEPTION:
	BEGIN
		DECLARE @Exception TABLE
		(
			Error binary(4),
			Source varchar(8000),
			Description varchar(8000),
			HelpFile varchar(8000),
			HelpID varchar(8000)
		)

		INSERT INTO @Exception EXEC sp_OAGetErrorInfo @WinTokken
		INSERT INTO	@Response (ResponseText)
		SELECT	( 
					SELECT	*
					FROM	@Exception
					FOR		JSON AUTO
				) AS ResponseText
	END

-----------------------
--FINALLY
-----------------------
RESULT:
--Dispose objects 
IF @WinTokken IS NOT NULL
	EXEC sp_OADestroy @WinTokken

-----------------------
--Result
-----------------------
SELECT	*  FROM	@Response

Refferences

• https://docs.microsoft.com/en-us/windows/desktop/winhttp/winhttp-versions
• https://docs.microsoft.com/en-us/windows/desktop/winhttp/iwinhttprequest-open
• https://docs.microsoft.com/en-us/windows/desktop/winhttp/iwinhttprequest-send
• https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oagetproperty-transact-sql

Related Links
Jupyter-notebook-start-up-folder