How can we help you today? How can we help you today?

How to Construct a URL for Webservices using variable parameters

OK I have a script which works and I want to turn this into a procedure, so I can call the procedure from an application. However I have 2 parameters within the script that form part of the URL that is used to call webservices. The said script is below.
My application will pass 2 parameters to the URL, namely date and staff.id. Both are highlighted in bold Italics in the script. Yes the script will return the correct values from the JSON output from the application concerned. So it all works as it stands.
Now I want to create this as a procedure to call from my application (a PowerApps application) but pass to the procedure both the date and staff.id. I have tried constructing the url by using declare statements and the parameters but did not work. 
Any thoughts ideas etc greatfully accepted.
cheers
Paul

DECLARE @response NVARCHAR(max);
  --get the data from the provider as JSON
    @response OUTPUT;
BEGIN
--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
PBA
0

Comments

1 comment

Sort by
  • PBA
    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......
    PBA
    0

Add comment

Please sign in to leave a comment.