spHTTPRequest

spHTTPRequest

To use the call, you will need to turn on OLE automation procedures

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Code

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spHTTPRequest]    Script Date: 7/25/2018 8:38:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spHTTPRequest] 
	-- Add the parameters for the stored procedure here
	@URI varchar(2000) = '',     
	@methodName varchar(50) = '',
	@requestBody varchar(MAX) = '',
	@ResponseText varchar(8000) OUTPUT,
	@StatusCode varchar(1000) OUTPUT,
	@StatusText varchar(1000) OUTPUT,
	@HttpHeaders varchar(MAX) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- Temporary declaration of variables, due to pulling them from procedure call
	DECLARE @SoapAction varchar(255), @UserName nvarchar(100), @Password nvarchar(100)

    -- Insert statements for procedure here
	IF    @methodName = ''
	BEGIN
		  select FailPoint = 'Method Name must be set'
		  return
	END

	set   @responseText = 'FAILED'

	DECLARE @objectID int
	DECLARE @hResult int
	DECLARE @source varchar(255), @desc varchar(255)

	EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT

	IF @hResult <> 0
	BEGIN
		  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
		  SELECT      hResult = convert(varbinary(4), @hResult),
					  source = @source,
					  description = @desc,
					  FailPoint = 'Create failed',
					  MedthodName = @methodName
		  goto destroy
		  return
	END

	EXEC sp_OASetProperty @objectID, 'setTimeouts','600000','600000','600000','600000'

	-- open the destination URI with Specified method
	EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password

	IF @hResult <> 0
	BEGIN
		  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
		  SELECT      hResult = convert(varbinary(4), @hResult),
				source = @source,
				description = @desc,
				FailPoint = 'Open failed',
				MedthodName = @methodName
		  goto destroy
		  return
	END

		IF @requestBody IS NOT NULL
	BEGIN
		declare @len int
		set @len = len(@requestBody)

		EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
		IF @hResult <> 0
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT      hResult = convert(varbinary(4), @hResult),
					source = @source,
					description = @desc,
					FailPoint = 'SetRequestHeader failed',
					MedthodName = @methodName
			  goto destroy
			  return
		END
	END


	-- if you have headers in a table called RequestHeader you can go through them with this
	-- split headers on pipe |, then split name/value on :
	IF @HttpHeaders <> ''
	BEGIN
		DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)

		DECLARE RequestHeader CURSOR
		LOCAL FAST_FORWARD
		FOR
			  SELECT LEFT(value,CharIndex(':', value)-1) as HeaderKey,RIGHT(value,Len(value)-CharIndex(':', value)) as HeaderValue
			  FROM dbo.Split(@HttpHeaders,'|')

		OPEN RequestHeader

		FETCH NEXT FROM RequestHeader
		INTO @HeaderKey, @HeaderValue

		WHILE @@FETCH_STATUS = 0
		BEGIN
			  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue
			  IF @hResult <> 0
			  BEGIN
					EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
					SELECT      hResult = convert(varbinary(4), @hResult),
						  source = @source,
						  description = @desc,
						  FailPoint = 'SetRequestHeader failed',
						  MedthodName = @methodName
					goto destroy
					return
			  END
			  FETCH NEXT FROM RequestHeader
			  INTO @HeaderKey, @HeaderValue
		END
		CLOSE RequestHeader
		DEALLOCATE RequestHeader
	END

	-- send the request
	EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
	IF    @hResult <> 0
	BEGIN
		  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
		  SELECT
			hResult = CONVERT(varbinary(4), @hResult),
			source = @source,
			description = @desc,
			FailPoint = 'Send failed',
			MedthodName = @methodName
		  goto destroy
		  return
	END

	-- Get status text
	exec sp_OAGetProperty @objectID, 'Status', @StatusCode out
	exec sp_OAGetProperty @objectID, 'StatusText', @StatusText out
	select @StatusCode, @statusText, @methodName

	-- Get response text
	exec sp_OAGetProperty @objectID, 'responseText', @responseText out

	IF @hResult <> 0
	BEGIN
		  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
		  SELECT      hResult = convert(varbinary(4), @hResult),
				source = @source,
				description = @desc,
				FailPoint = 'ResponseText failed',
				MedthodName = @methodName
		  goto destroy
		  return
	END

	destroy:
		  exec sp_OADestroy @objectID

END