Is it Possible to declare Cursor within IF Condition in MySQL UDF

Question

Is it possible for me to Declare cursor inside if statement?

if possible how could i make it?

because i just made cursor like this

CREATE FUNCTION `fn_test`(
    ProductID BIGINT(20)
)
RETURNS DECIMAL(10,2)

BEGIN
DECLARE PrductDiscValue DECIMAL(10, 2);
DECLARE DiscType INT(1);
DECLARE DiscValue DESIMAL(10,2);

IF ProductID != 0 THEN 
    SET PrductDiscValue = (SELECT Discountvalue, DiscountType FROM discount WHERE FIND_IN_SET(ProductID,DiscIDs);
END IF;

RETURN ProductDiscountValue(1);

But this is not working. So, I do the following

IF ProductID != 0 THEN 
    DECLARE PrductDiscValue CURSOR FOR SELECT Discountvalue, DiscountType FROM discount WHERE FIND_IN_SET(ProductID,DiscIDs;
END IF;

OPEN ProductDiscountValue;
FETCH ProductDiscountValue INTO DiscValue, DiscType; 
RETURN DiscValue;
END

And this gives me error ::

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE ProdDiscValue CURSOR FOR SELECT Discountvalue, DiscountType FROM discount' at line 16.

I need the both DiscValue and DiscType for different calculation.

Any Help would be appreciate

Thanks in advance.

Answer 1

Please, use it:

BEGIN

IF test = true THEN
    BEGIN 
        DECLARE ats_cursor CURSOR FOR SELECT distinct ats_id FROM ats_eb ats where  ats.year = year and  ats.edition_shortname = edition;
    END;
ELSE
    BEGIN 
        DECLARE ats_cursor CURSOR FOR SELECT distinct ats_id FROM ats_eb ats where  ats.year = year and  ats.edition_shortname = edition;
    END;
END IF;

END

Answer 2

even I never used CURSOR so far in MySQL, But what I found is that , it is clearly mentioned on MySQL manual

Cursor declarations must appear before handler declarations and after variable and condition declarations.

if you want to use that cursor on some condition then you must use FETCH CURSOR syntax in IF condition

below are some good tutorial which will describe the cursor in details:

CURSOR in mySQL stored procedure

MySQL CURSOR Tutorial