SQL Server

This document contains a stored procedure written for SQL Server 2014, using WinHttp.WinHttpRequest.5.1

CREATE PROCEDURE [dbo].[ProfitAPI_GetInvoices] ( @page INT )
AS
BEGIN

DECLARE @url VARCHAR(50)
DECLARE @win INT
DECLARE @HttpWinRequest INT
DECLARE @ResultText VARCHAR (MAX)

SET @url = 'https://api.profit365.eu/1.6/sales/invoices/' + CONVERT (VARCHAR(100), @page);

EXEC @HttpWinRequest = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @HttpWinRequest = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false'

-- specify response type to JSON, alternate is text/xml
EXEC @HttpWinRequest = sp_OAMethod @win, 'setRequestHeader', NULL, 'Content-Type', 'application/json'

EXEC @HttpWinRequest = sp_OAMethod @win, 'setRequestHeader', NULL, 'ClientID', 'enter-your-client-id-here'
EXEC @HttpWinRequest = sp_OAMethod @win, 'setRequestHeader', NULL, 'ClientSecret', 'enter-your-client-secret-here'
EXEC @HttpWinRequest = sp_OAMethod @win, 'setRequestHeader', NULL, 'Authorization', 'enter-your-authorization-header-here'
EXEC @HttpWinRequest = sp_OAMethod @win, 'setRequestHeader', NULL, 'CompanyID', 'enter-your-company-id-here'

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @HttpWinRequest=sp_OAMethod @win, 'Send'

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
DECLARE @status INT

EXEC @HttpWinRequest=sp_OAGetProperty @win, 'Status', @status OUTPUT

CREATE TABLE #tmp(_response NVARCHAR(MAX))
INSERT INTO #tmp EXEC @HttpWinRequest =sp_OAGetProperty @win, 'ResponseText'
SELECT @ResultText = _response FROM #tmp
DROP TABLE #tmp

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @HttpWinRequest = sp_OADestroy @win

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
SELECT @status AS [Status], @ResultText AS [Response] END

GO

EXEC dbo.[ProfitAPI_GetInvoices] 1

Back to top ↑