CODEWITHSUNDEEP
informix
Shadam
Shadam

Reputation: 1122

Use MERGE statement in a stored procedure with Informix

I have a stored procedure like this : http://tinyurl.com/pbk8qfb

CREATE PROCEDURE PS_TAB_TSEN(NOMFIC VARCHAR(70), LIBSEN VARCHAR(35),
                             CODAPPFOF VARCHAR(3))

    DEFINE NUMINTNAV VARCHAR(10);

    BEGIN;

    LET NUMINTNAV = '0';
    SELECT TO_CHAR(NUM_INT_NAV) INTO NUMINTNAV
      FROM TAB_INAV WHERE NOM_FIC = NOMFIC;

    MERGE INTO TAB_TSEN T
    USING (SELECT COD_SEN, LIBSEN AS LIB_SEN, CODAPPFOF AS COD_APP_FOF 
             FROM TAB_TSEN 
            WHERE COD_SEN = NUMINTNAV) AS S
    ON (T.COD_SEN = S.COD_SEN)
    WHEN MATCHED THEN
            UPDATE SET T.COD_SEN = S.COD_SEN, T.LIB_SEN = S.LIB_SEN,
                       T.COD_APP_FOF = S.COD_APP_FOF
    WHEN NOT MATCHED THEN 
            INSERT (T.COD_SEN, T.LIB_SEN, T.COD_APP_FOF)
            VALUES (S.COD_SEN, S.LIB_SEN, S.COD_APP_FOF);

    COMMIT;

END PROCEDURE;

When I run it all is good, but when I check in the table I see no result, for the insert statement and for the update statement too.

I call it like this:

CALL PS_TAB_TSEN('~/PSS/Order/OrderList.aspx', 'Liste des commandes', 'NET');

I don't understand what is going on...

Upvotes: 1

Views: 1003

Answers (1)

Shadam
Shadam

Reputation: 1122

Problem solved, I replace my stored procedure by this one :

CREATE PROCEDURE PS_TAB_TSEN(NOMFIC VARCHAR(70), LIBSEN VARCHAR(35), CODAPPFOF VARCHAR(3))
    DEFINE ISPRESENT INTEGER;
    DEFINE NUMINTNAV VARCHAR(10);

    BEGIN;

    LET NUMINTNAV = '0';
    SELECT TO_CHAR(NUM_INT_NAV) INTO NUMINTNAV FROM TAB_INAV WHERE NOM_FIC = NOMFIC;
    LET ISPRESENT = 0;
    SELECT COUNT(*) INTO ISPRESENT FROM TAB_TSEN WHERE COD_SEN = NUMINTNAV;

    IF(ISPRESENT > 0) THEN
        UPDATE TAB_TSEN SET T.COD_SEN = S.COD_SEN, T.LIB_SEN = S.LIB_SEN, T.COD_APP_FOF = S.COD_APP_FOF
    ELSE
        INSERT INTO TAB_TSEN (T.COD_SEN, T.LIB_SEN, T.COD_APP_FOF) VALUES (S.COD_SEN, S.LIB_SEN, S.COD_APP_FOF);
    END IF;

    COMMIT;

END PROCEDURE;

Upvotes: 1

Related Questions