How to pass infotable to stored procedure for inserting data in MySQL

[Vipul_Oswal]

hi @jensc 

I am trying to do the same thing in SQL , updating data from infotable to MySQL table.

Above logic should be written in javascript service? If yes, How can i call below exec SP here? 

exec [StoredProcedure] @json = [[StringArray]]

[jensc]

Hello @Vipul_Oswal,

Let me clarify it a little bit:

Regular thing: Javascript Service:

let jsonString = InfoTable.toJSON();
let stringArray = jsonString.rows;

Things["YourDatabaseThing"].UpdateDataBaseWithJSON({
      StringArray: stringArray
});

SQL thing: SQL Command Service:

exec [StoredProcedure] @json = [[StringArray]]

Example Stored Procedure:

ALTER procedure [dbo].[_InsertPersonData]
	@json NVARCHAR(max)
as
begin
    MERGE INTO [User] AS target
    USING (
        SELECT 
        distinct PersonnelNumber, FirstName, LastName, PlantUID
        FROM OPENJSON(@json, '$.rows')
        WITH (
            OrgID NVARCHAR(256) '$.OrgID',
            PersonnelNumber NVARCHAR(256) '$.PersonnelNumber',
            FirstName NVARCHAR(256) '$.FirstName',
            UserRole NVARCHAR(256) '$.UserRole',
            IsHybridArea bit '$.IsHybridArea',
            UserOrgType NVARCHAR(256) '$.UserOrgType',
            OrgName NVARCHAR(256) '$.OrgName',
            LastName NVARCHAR(256) '$.LastName',
            PlantUID NVARCHAR(256) '$.PlantUID',
            CostCenterID NVARCHAR(256) '$.CostCenterID'
        )
    ) AS source
    ON target.personid = source.PersonnelNumber
    WHEN MATCHED THEN
        UPDATE SET 
            plantuid = source.PlantUID,
			firstname = source.FirstName,
			lastname = source.LastName,
			isactive = '1',
			updatedby = 'System User',
			updateddate = getdate(),
			shiftuid = '1',
            personid = source.PersonnelNumber
    WHEN NOT MATCHED THEN
        INSERT (plantuid,personid,firstname,lastname,roleuid,isactive,shiftuid,createdby,createddate)
        VALUES (source.PlantUID, source.PersonnelNumber, source.FirstName, source.LastName,'7','1','1','System User',getdate())
    WHEN NOT MATCHED BY SOURCE THEN
        UPDATE SET target.isactive = 0;
end

Your stored procedure might look different as you are using MySQL.

Regards,

Jens