I wanted to create a stored procedure in SQL Server to complete a POST request. The request would take two parameters.
Curl that I've used for Postman:
curl -X 'POST' \
'https://link' \
-H 'accept: application/json' \
-H 'Authorization: Bearer somepassword' \
-H 'Content-Type: application/json' \
-d '{
"userName": "username",
"password": "password"
}'
I've tried writing the code below, but it doesn't seem to work...
DECLARE @token INT;
DECLARE @ret INT;
DECLARE @url NVARCHAR(MAX);
DECLARE @apiKey NVARCHAR(32);
DECLARE @json AS TABLE(Json_Table NVARCHAR(MAX))
DECLARE @userName varchar(max)
DECLARE @password varchar(max)
DECLARE @body varchar(max)
SET @url = 'https://link'
SET @userName = 'username'
SET @password = 'password'
SET @body = '
{
"userName": "'+@username+'",
"password": "'+@password+'"
}
'
EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0
RAISERROR('Unable to open HTTP connection.', 10, 1);
-- This calls the necessary methods.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'send', null, @body
-- Grab the responseText property, and insert the JSON string into a table temporarily. This is very important, if you don't do this step you'll run into problems.
INSERT INTO @json (Json_Table)
EXEC sp_OAGetProperty @token, 'responseText'
-- This is all I have to add to insert into a non-existing table.
SELECT *
FROM OPENJSON((SELECT * FROM @json))
