What's wrong with this stored procedure ?

Question

Hello guys, getting really close to running my first stored procedure. This one compiles but when I run it with call test.fttest5('YEAR'); it throws an error

SQL State: 22001 Vendor Code: -303 Message: [SQL0303] Host variable *N not compatible. Cause . . . . . : A FETCH, SELECT, CALL, SET, VALUES INTO, GET DIAGNOSTICS, GET DESCRIPTOR, or SET DESCRIPTOR cannot be performed because the data type of host variable *N is not compatible with the data type of the corresponding list item.

Also how do I specify an unlimited data type in my stored procedure ? I tried DECLARE temp VARCHAR(MAX); but it did not worked. My platform is ISeries DB2 V5R4.

create procedure test.fttest5
    (IN ftExpression CHARACTER(30))
    language sql
    reads sql data
    dynamic result sets 1
    begin

    declare cmd VARCHAR(50);
    declare whr VARCHAR(50)
;

    declare x cursor for sl;
    set cmd='select * from testSchema.tempTable' ;
    if ftExpression IS NOT NULL
 THEN
     set whr= ftExpression;
    END IF;

    set cmd=cmd || CASE WHEN whr IS NULL THEN '' ELSE ' ORDER BY ' || whr END;  
    prepare sl from cmd;
    open x;
    return;
    end
    ;

Answer 1

Selecting * is bad, especially if you are selecting from the complex or big views.

Cursors are bad in general because you iterate through each row, instead of working with sets.

Also think whether there is a need for the dynamic SQL here at all?

What is the purporse of this stored procedure? It looks rather simple and I'm wondering whether you can re-write it by using sets, instead of iterating with the cursor?

Answer 2

I believe the error you are getting here is actually occurring before your procedure is even being invoked. You've got the procedure defined with a parameter that's a CHAR but you are calling it with a VARCHAR. When you type SQL in interactively like that parameters come through as a VARCHAR.

Try calling it like this: call test.fttest5(CHAR('YEAR'))

Or you could change the parameter to be a VARCHAR too, though you would still want to test calling it with a CHAR to see what would happen.

However I think you are missing the SET RESULT SETS CURSOR x line. Also I'd put the prepare before the declare since I think the order might be important.

The IBM documentation is at http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzaik/rzaikspresultsets.htm.