SQL Schedular with Http Request

********** Hit the http request through sql job and Enabling commands ************

*********** FIrst : Enabling Configurations to hit the url through sql ************* 

sp_configure 'show advanced options'

,            1

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures'

,            1

GO

RECONFIGURE;

GO

sp_configure 'show advanced options'

,            1

GO

RECONFIGURE;

*********************** END ****************************

************ GET Http scalar function *************

USE [Nobolo]

GO

/****** Object:  UserDefinedFunction [dbo].[GetHttp]    Script Date: 7/8/2020 1:26:35 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER function [dbo].[GetHttp] ( @url varchar(8000) )

returns varchar(8000)

as

BEGIN

DECLARE @win int

DECLARE @hr int

DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1'

,                    @win OUT

IF @hr <> 0

EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win

,                    'Open'

,                    NULL

,                    'GET'

,                    @url

,                    'false'

IF @hr <> 0

EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win

,                    'Send'

IF @hr <> 0

EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win

,                         'ResponseText'

,                         @text OUTPUT

IF @hr <> 0

EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win

IF @hr <> 0

EXEC sp_OAGetErrorInfo @win

RETURN @text

END

********************************************

********* sp that will execute in sql job *************

USE [Nobolo]

GO

/****** Object:  StoredProcedure [dbo].[SpSchedularPayoutProcess]    Script Date: 7/8/2020 1:27:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

---

-- [dbo].[SpSchedularRecurringProcess] ''

ALTER PROCEDURE [dbo].[SpSchedularPayoutProcess]

@ReturnMessage as VARCHAR(200) = null output

AS

BEGIN

Declare  @Url as varchar(max) = null

,@returnVal as varchar(200) = null

,@BaseUrl                   as varchar(300)         = 'http://staging3.sdsol.com/Nobolo/'

--,@BaseUrl                   as varchar(300)         = 'http://localhost:65451/'

SET @url= @BaseUrl + 'api/v1/Schedular/ProcessCompletedSessions'

-- Log the call

Insert into tblSchedularLog (LogDescription, RecordStatus)

values  (@url,'PayoutProcess')

--Call the API

SELECT @returnVal = dbo.GetHttp(@url)

End

************* END *********************