Comments
Sort by recent activity
I have an answer from an alternate source for anyone interested. Take the code above and here it is. The convert statements enable the passing of parameters into the URL construct. create procedure dbo.MyProcedure ( @Date date , @StaffId numeric ) as begin DECLARE @response NVARCHAR(max), @Url varchar(max); set @Url = 'https://powercomm.simprosuite.com/api/v1.0/companies/1/schedules/?access.token=383abc4084a2b8dcbf508252e4a0313762fd623b&Date=' + convert(varchar(10), @Date, 23) + '&Staff.ID=' + convert(varchar(3),@StaffId); --get the data from the provider as JSON EXECUTE dbo.GetWebService @Url, @response OUTPUT; --Insert into Schedules (SchedID, Type, JobNo, TotalHrs, EmployeeID, SchedDate, StartTime, FinishTime) select SchedID, Type, JobNo, TotalHrs, EmployeeID, SchedDate, StartTime, FinishTime FROM OpenJson(@response) WITH (SchedID numeric N'$.ID', Type CHAR(15) N'$.Type', JobNo nvarchar(5) N'$.Reference', TotalHrs Dec(4,2) N'$.TotalHours', SchedDate Date N'$.Date', EmployeeID numeric(6) N'$.Staff.ID', Blocks nvarchar(max) N'$.Blocks' as JSON) OUTER APPLY OpenJson(Blocks) WITH (StartTime datetimeoffset N'$.ISO8601StartTime', FinishTime datetimeoffset N'$.ISO8601EndTime' ); END go Enjoy...... / comments
I have an answer from an alternate source for anyone interested.Take the code above and here it is. The convert statements enable the passing of parameters into the URL construct.create procedure d...