Call API or WebService inside SQLServer Procedure

authenticate-post.sql

CREATE PROCEDURE [dbo].[AUTHENTICATE]
	@USER_NAME NVARCHAR(60),
	@PASSWORD NVARCHAR(60),
	@USER_ID INT
AS 
BEGIN
	DECLARE
		@URL NVARCHAR(65) = 'https://{api_url}/login',
		@OBJECT AS INT,
		@JSON NVARCHAR(150),
		@RESPONSE AS NVARCHAR(150),
		@RESPONSE_TO_SEND AS NVARCHAR(150),
		@BODY AS VARCHAR(500);
	
		SET @BODY = CONCAT(
		'{
			"username": "', @USER_NAME, '",
			"password": "', @PASSWORD, '"
		}')

		EXEC sp_OACreate 'MSXML2.XMLHTTP', @OBJECT OUT;
		EXEC sp_OAMethod @OBJECT, 'open', NULL, 'post', @URL, 'false';
		EXEC sp_OAMethod @OBJECT, 'setRequestHeader', null, 'Content-Type', 'application/json';
		EXEC sp_OAMethod @OBJECT, 'send', null, @BODY;
		EXEC sp_OAMethod @OBJECT, 'responseText', @RESPONSE OUTPUT;

		IF CHARINDEX('false', (SELECT @RESPONSE)) > 0
		BEGIN
			SET @RESPONSE = 'NO DATA WAS FOUND'
			SELECT @RESPONSE AS RESPONSE;
		END
		ELSE
		BEGIN
			IF @RESPONSE = ''
			BEGIN
				SELECT 'PASSWORD OR USERNAME NOT FOUND' AS RESPONSE;
			END
			ELSE 
			BEGIN
				SET @JSON = (SELECT @RESPONSE);
				IF ISJSON(@JSON) = 1
				BEGIN
					IF JSON_VALUE(@JSON, '$.error') IS NOT NULL
					BEGIN	
						SELECT JSON_VALUE(@JSON, '$.error') AS RESPONSE;
					END

					IF JSON_VALUE(@JSON, '$.bearer') IS NOT NULL
					BEGIN				 
						SET @RESPONSE_TO_SEND = (SELECT JSON_VALUE(@JSON, '$.bearer'));
						SELECT 'SUCCESS LOGIN' AS RESPONSE;
					END
				END
				ELSE
				BEGIN
					SELECT 'INVALID JSON' AS RESPONSE;
				END			
			END
		 
			SELECT @RESPONSE AS RESPONSE_COMPLETE		
		END
	EXEC sp_OADestroy @OBJECT
END;

authorize-get.sql

CREATE PROCEDURE [dbo].[AUTHORIZE] 
	@TOKEN NVARCHAR(1000)
AS
BEGIN
	DECLARE
		@URL NVARCHAR(65) = 'https://{api_url}/login',
		@OBJECT AS INT,
		@RESPONSE AS VARCHAR(2500),
		@JSON NVARCHAR(2500),
		@SYSTEM NVARCHAR(10);

		SET @SYSTEM = '{another_parameter}'

		EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
		EXEC sp_OAMethod @OBJECT, 'open', NULL, 'get', @URL, 'false';
		EXEC sp_OAMethod @Object, 'setRequestHeader', null, '{another_parameter}', @SYSTEM;
		EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'authorization', @TOKEN;
		EXEC sp_OAMethod @OBJECT, 'send';
		EXEC sp_OAMethod @OBJECT, 'responseText', @RESPONSE OUTPUT;
	
		IF((SELECT @RESPONSE) <> '')
		BEGIN
			 SET @JSON = (SELECT @RESPONSE);
			 IF ISJSON(@JSON) = 1
			 BEGIN

				IF JSON_VALUE(@JSON, '$.error') IS NOT NULL
				BEGIN				 
					SELECT JSON_VALUE(@JSON, '$.error') AS RESPONSE;
				END

				IF JSON_VALUE(@JSON, '$.property') IS NOT NULL
				BEGIN
					SELECT JSON_VALUE(@JSON, '$.property') AS RESPONSE;
				END

			 END
			 ELSE 
			 BEGIN
				SELECT 'INVALID JSON' AS RESPONSE;			
			 END
		END
		ELSE
		BEGIN
			 SELECT 'NO DATA WAS FOUND' AS RESPONSE_COMPLETE;
		END
		SELECT @RESPONSE AS RESPONSE_COMPLETE;
		EXEC sp_OADestroy @OBJECT
END;

notes.sql

-- before run the prcedures, note that you must active the external connection in Sql Server (prefer 2016 or above 🤠)
sp_configure 'show advanced options', 1;
GO
  RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
  RECONFIGURE;
GO

-- another thing, the compatibility level of the database that you are trying to run the procedures, must be greater than `130` (default value for 2016 version)
SELECT compatibility_level
FROM sys.databases
WHERE name = 'DataBaseName';

update data-put.sql

CREATE PROCEDURE [dbo].[PUT_DATA] 
	@END_POINT INT,
	@DATA01 INT,
	@DATA02 FLOAT,
	@TOKEN_VALUE NVARCHAR(1000)
AS 
BEGIN
	DECLARE 
		@URL NVARCHAR(255),
		@OBJECT AS INT, 
		@RESPONSE AS VARCHAR(8000),
		@BODY AS VARCHAR(8000),		
		@TOKEN NVARCHAR(1000),
		@AUTH_TYPE NVARCHAR(15);

		SET @URL = CONCAT('https://{api_url}/login/', @END_POINT);
		SET @AUTH_TYPE = 'Bearer';
		SET @TOKEN = CONCAT(@AUTH_TYPE, @TOKEN_VALUE);
		SET @BODY  = CONCAT(
			'{
				"data": [
					{
						"property_01": ', @DATA01, ',
						"property_02": ', @DATA02, '
					}
				]
			}')

		EXEC sp_OACreate 'MSXML2.XMLHTTP', @OBJECT OUT;
		EXEC sp_OAMethod @OBJECT, 'open', NULL, 'put', @URL, 'false'
		EXEC sp_OAMethod @OBJECT, 'setRequestHeader', NULL, 'authorization', @TOKEN
		EXEC sp_OAMethod @OBJECT, 'setRequestHeader', NULL, 'Content-Type', 'application/json'
		EXEC sp_OAMethod @OBJECT, 'send', NULL, @BODY
		EXEC sp_OAMethod @OBJECT, 'responseText', @RESPONSE OUTPUT

		IF CHARINDEX('false', (SELECT @RESPONSE)) > 0
		BEGIN
			SELECT @RESPONSE AS 'MESSAGE' 
		END
		ELSE
		BEGIN
			IF @RESPONSE = ''
			BEGIN
				SELECT @RESPONSE = 'DATA UPDATE WITH SUCCESS!'
			END

			SELECT @RESPONSE AS 'STATUS'
		END
	EXEC sp_OADestroy @OBJECT
END;