How to send http POST request from sql server stored procesdure.

enableOleAutomation.sql

--This query enables ole automation procedures.  set 0 to disable
exec master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

--OLE automation disabled the following error is thrown.
--SQL Server blocked access to procedure 'sys.sp_OACreate' of component
--'Ole Automation Procedures' because this component is turned off as part 
--of the security configuration for this server. 
--A system administrator can enable the use of 'Ole Automation Procedures'
--by using sp_configure. For more information about enabling 'Ole Automation Procedures',
--search for 'Ole Automation Procedures' in SQL Server Books Online.

--Running the above query may result to this erro
--The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.
--To fix this error run the query below and the enable OLE automation


exec master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

webRequest.sql

--create/alter a stored proceudre  accordingly
create procedure webRequest
as 
DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
DECLARE @Authorization NVARCHAR(200);

--set your post params
SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
--SET @contentType = 'application/x-www-form-urlencoded';
SET @contentType = 'application/json';
--SET @postData = 'KeyValue1=value1&KeyValue2=value2'
SET @postData = '{ "Source": "DATABASE",  "CreatedDate": "2022-02-24",  "Test": "TEST" }'
SET @url = 'set your webhook url end point here'

-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
--set a custom header Authorization is the header key and VALUE is the value in the header
--EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'Authorization', 'VALUE'

--EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
go