Samuele Locatelli
38 lines
1.4 KiB
SQL
38 lines
1.4 KiB
SQL
DROP PROCEDURE IF EXISTS `stp_mergeLogMachine`;
|
|
|
|
CREATE DEFINER=`steamware`@`10.74.%` PROCEDURE `stp_mergeLogMachine`(
|
|
IN `pKeyNum` INT,
|
|
IN `pMachineID` INT,
|
|
IN `pProjDbId` INT,
|
|
IN `pDtEvent` DATETIME,
|
|
IN `pEvType` INT,
|
|
IN `pSupervId` VARCHAR(250),
|
|
IN `pVarValue` VARCHAR(250)
|
|
)
|
|
LANGUAGE SQL
|
|
NOT DETERMINISTIC
|
|
CONTAINS SQL
|
|
SQL SECURITY DEFINER
|
|
COMMENT 'Inserimento record in table LogMachine qualora non esistesse'
|
|
BEGIN
|
|
|
|
# do x scontata cancellazione, creo nuovo record!
|
|
INSERT INTO LogMachine(KeyNum, MachineID, ProjDbId, DtEvent, EvType, SupervId, VarValue)
|
|
VALUES (pKeyNum, pMachineID, pProjDbId, pDtEvent, pEvType, pSupervId, pVarValue);
|
|
|
|
## creo record se non fosse già presente
|
|
#INSERT INTO LogMachine(KeyNum, MachineID, ProjDbId, DtEvent, EvType, SupervId, VarValue)
|
|
#SELECT src.* FROM
|
|
#(SELECT pKeyNum AS KeyNum , pMachineID AS MachineID, pProjDbId AS ProjDbId, pDtEvent AS DtEvent, pEvType AS EvType, pSupervId AS SupervId, pVarValue AS VarValue) as src
|
|
# LEFT OUTER JOIN LogMachine tgt ON
|
|
# tgt.KeyNum = src.KeyNum
|
|
# AND tgt.MachineID = src.MachineID
|
|
# AND tgt.ProjDbId = src.ProjDbId
|
|
# AND tgt.DtEvent = src.DtEvent
|
|
# AND tgt.EvType = src.EvType
|
|
# AND tgt.SupervId = src.SupervId
|
|
# AND tgt.VarValue = src.VarValue
|
|
#WHERE tgt.DbId IS NULL;
|
|
##ON DUPLICATE KEY UPDATE DbId = DbId;
|
|
|
|
END |