MySQL Forums
Forum List  »  French

Re: Procedure stocké d'insertion
Posted by: Jean Molliné
Date: March 28, 2017 02:44PM

L'erreur vient de la syntaxe employée pour le INSERT/SELECT, il y a un VALUES en trop.

Remplacer par :
CREATE DEFINER = `root`@`localhost` PROCEDURE `formulairedemamde_Insert` (motifDemamde varchar(100), vehicule int, demandeur int, immatriculationNumber varchar(45), creationUserId int)
BEGIN
  INSERT INTO `gesparc`.`formulairedemamde` (`id`,
    `motifDemamde`,
    `vehicule`,
    `demandeur`,
    `immatriculationNumber`,
    `creationDate`,
    `creationUserId`,
    `lastModifdate`,
    `lastmodifUserId`
  )
  SELECT
    NULL AS id,
    motifDemamde,
    vehicule,
    demandeur,
    CONCAT(
    prefixe,
    '-',
    LPAD(IF(t.numero = 9999, 0, t.numero + 1), 4, '0'),
    '-',
    IF(t.numero = 9999, prochain_suffixe, suffixe)
    ) AS nouvelle_plaque,
    current_timestamp,
    creationUserId,
    current_timestamp,
    creationUserId
  FROM (SELECT
      immatriculationNumber,
      MID(immatriculationNumber, 1, 2) AS prefixe,
      MID(immatriculationNumber, 4, 4) + 0 AS numero,
      MID(immatriculationNumber, 9, 2) AS suffixe,
      IF(
      MID(immatriculationNumber, 10, 1) = 'Z',
      CONCAT(CHAR(ASCII(MID(immatriculationNumber, 9, 1)) + 1), 'A'),
      CONCAT(MID(immatriculationNumber, 9, 1), CHAR(ASCII(MID(immatriculationNumber, 10, 1)) + 1))
      ) AS prochain_suffixe
    FROM formulairedemamde
    ORDER BY suffixe DESC, numero DESC
    LIMIT 1) AS t;
END

______________________________________________________________
Une question bien formulée, c'est un problème bien compris : ça représente déjà les 3/4 de la réponse ;)

Options: ReplyQuote


Subject
Views
Written By
Posted
596
March 28, 2017 11:07AM
Re: Procedure stocké d'insertion
299
March 28, 2017 02:44PM
273
March 28, 2017 03:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.