Htykuut

I have a script which works. 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.

The script:

DECLARE @response NVARCHAR(max);

  --get the data from the provider as JSON

  EXECUTE dbo.GetWebService 'https://powercomm.simprosuite.com/api/v1.0/companies/1/schedules/?access.token=383abc4084a2b8dcbf508252e4a0313762fd623b&Date=***2018-11-21***&Staff.ID=***249***',

    @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

I've assumed your query all works, and I've just encapsulated it within a SP:

create procedure dbo.MyProcedureName

(

  @Date date

  , @StaffId int

)

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(38),@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