Skip to content

Instantly share code, notes, and snippets.

@Bumboobee
Last active September 8, 2023 03:10
Show Gist options
  • Save Bumboobee/6c7840d2b00cd41922ae5905b5ba3422 to your computer and use it in GitHub Desktop.
Save Bumboobee/6c7840d2b00cd41922ae5905b5ba3422 to your computer and use it in GitHub Desktop.
Call API or WebService inside SQLServer Procedure
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;
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;
-- 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';
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment