Informix, Initializing variables in a stored procedure

Question

I have a stored procedure on Informix database. I want to get some variables from the execution of that procedure.

I don't know how to create the stored procedure.

This is what I have so far:

CREATE PROCEDURE foo()
RETURN somebar;
END PROCEDURE;

When I run it, I get an error:

DEFINE somebar

I want to do this:

execute procedure foo() into bar;

How to I execute procedure foo() and put a return value into bar?

Answer 1

Here's an illustration. Basically, the RETURNING clause lists types (optionally followed by AS name - but the name does not declare a variable); then in the body of the procedure you define variables 'as usual' and then return them.

This particular example is a simulation of Oracle's NEXT_DAY function, minus globalization. The file includes a self-test:

Example Procedure

-- @(#)$Id: next_day.spl,v 1.1 2004/10/05 21:39:18 jleffler Exp $
--
-- @(#)Create procedure equivalent to Oracle's NEXT_DAY() function.
-- Bugs: not internationalized.

CREATE PROCEDURE 'oracle'.next_day(dateval DATE, dayname CHAR(3))
    RETURNING DATE AS next_date;

    DEFINE rv DATE;     -- Return value.
    DEFINE dw INTEGER;  -- Weekday corresponding to dayname.
    DEFINE wd INTEGER;  -- Weekday corresponding to dateval.
    DEFINE dn CHAR(3);

    LET rv = NULL;

    IF dateval IS NOT NULL THEN
        LET dw = NULL;
        LET dn = UPPER(dayname);
        IF   dn = 'SUN' THEN LET dw = 0;
        ELIF dn = 'MON' THEN LET dw = 1;
        ELIF dn = 'TUE' THEN LET dw = 2;
        ELIF dn = 'WED' THEN LET dw = 3;
        ELIF dn = 'THU' THEN LET dw = 4;
        ELIF dn = 'FRI' THEN LET dw = 5;
        ELIF dn = 'SAT' THEN LET dw = 6;
        END IF;
        IF dw IS NOT NULL THEN
            LET wd = WEEKDAY(dateval);
            LET rv = dateval - wd + dw;
            IF wd >= dw THEN
                LET rv = rv + 7;
            END IF;
        END IF;
    END IF;

    RETURN rv;

END PROCEDURE;

Self-Test Code

--     February 2001
--   S  M Tu  W Th  F  S
--               1  2  3
--   4  5  6  7  8  9 10
--  11 12 13 14 15 16 17
--  18 19 20 21 22 23 24
--  25 26 27 28

CREATE TEMP TABLE test_next_day
(
    ref_date    DATE NOT NULL,
    nxt_day     CHAR(9) NOT NULL,
    exp_date    DATE NOT NULL
);

INSERT INTO test_next_day VALUES(MDY(02,01,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,03,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,04,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,05,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,06,2001), 'Tuesday',   MDY(02,13,2001));
INSERT INTO test_next_day VALUES(MDY(02,07,2001), 'Tuesday',   MDY(02,13,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Monday',    MDY(02,05,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Wednesday', MDY(02,07,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Thursday',  MDY(02,08,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Friday',    MDY(02,09,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Saturday',  MDY(02,03,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Sunday',    MDY(02,04,2001));

SELECT ref_date, nxt_day, WEEKDAY(ref_date) AS ref_dow,
       exp_date, WEEKDAY(exp_date) AS exp_dow,
       NEXT_DAY(ref_date, nxt_day) AS got_date
    FROM test_next_day
    INTO TEMP test_next_day2;

SELECT *
    FROM test_next_day2
    ORDER BY ref_date, nxt_day;

SELECT "**FAILED**" AS failed, *
    FROM test_next_day2
    WHERE (exp_date IS NOT NULL AND got_date IS     NULL)
       OR (exp_date IS     NULL AND got_date IS NOT NULL)
       OR (exp_date IS NOT NULL AND got_date IS NOT NULL AND exp_date != got_date)
    ORDER BY ref_date, nxt_day;