Erik,


Erik De Laet schrieb:

> I want to select different fields of a table
> depending on a parameter to the stored procedure.
>
> Is this possible ?

Neither a full YES nor a full NO.
See below.

> I have written a stored procedure which performs a select on this table.
>
> But I would like it to return 'CURSUS_DESCR_NL'
> when I pass it a parameter 'NL' and
> 'CURSUS_DESCR_EN' when I pass it a parameter 'EN'.
>
> I would like to use a select in the stored proc as f.i.
>
> select CURSUS_CODE as Code, CURSUS_DESCR_<nl or en> as Descr from TheTable.
>
> By the way: this is a simplified example of
> course, so I would not like to use an
> IF-statement to use different select-statements depending on the parameter.
>
> So I am asking for the way to something like
> this. Not the exact solution to the above problem.

The only option you've got is to use EXECUTE STATEMENT, where you can
put together the SQL you need.


CREATE PROCEDURE SP_GET_THIS_OR_THAT (
SUFFIX VARCHAR(2)
)
RETURNS (
CURSUS_CODE WHATEVER_TYPE,
CURSUS_DESCR VARCHAR(255)
)
AS
DECLARE VARIABLE FIELD_TO_SELECT VARCHAR(31);
DECLARE VARIABLE MY_STATEMENT VARCHAR(100);
/* adjust the length according to your needs */
BEGIN

MY_STATEMENT = 'SELECT CURSUS_CODE, CURSUS_DESCR_' ||
:SUFFIX ||
' FROM CURSUSTYPE';

FOR EXECUTE STATEMENT :MY_STATEMENT
INTO :CURSOR_CODE, :CURSUS_DESCR
DO BEGIN
SUSPEND;
END
END

WATCH OUT:
----------
The Statement will be prepared EVERY time (inside the DO BEGIN .. END)
which will consume quite a lot of memory on big resultsets.
The Statement will not CHECK, if the SQL is valid.

HTH

Luc.