Stored Procedure - create a cursor - accept an OUT parameter

Viewed 285

I was hoping to get some help the the below question, unfortunately the script I have created isn't working. Any assistance would be greatly appreciated!

Question: Write a script that creates a stored procedure named test. This stored procedure should create a cursor for a result set that consists of the product_name and list_price columns for each product with a list price that’s greater than $700. The rows in this result set should be sorted in descending sequence by list price. The stored procedure should accept an OUT parameter where a message is passed out of the procedure. Then, the procedure should set the out parameter to a string variable that includes the product_name and list price for each product so it looks something like this: Gibson SG,2517.00|Gibson Les Paul,1199.00| Here, each value is enclosed in asterisk(*), each column is separated by a comma (,) and each row is separated by a pipe character (|).

My script:

CREATE PROCEDURE test( OUT message VARCHAR(200) )
BEGIN
DECLARE product_name_var VARCHAR(50);
DECLARE list_price_var DECIMAL(9,2);
DECLARE row_not_found TINYINT DEFAULT FALSE;
DECLARE s_var VARCHAR(400) DEFAULT '';

DECLARE invoice_cursor CURSOR for
    SELECT 
        product_name,
        list_price
    FROM
        products
    WHERE
        list_price > 700
    ORDER BY list_price DESC;
    
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET row_not_found = TRUE;

OPEN invoice_cursor;

FETCH invoice_cursor INTO product_name_var, list_price_var;
WHILE row_not_found = FALSE DO

SET s_var = CONCAT(s_var,'*', product_name_var,'*,*',list_price_var,'*|');
    FETCH invoice_cursor INTO product_name_var, list_price_var;
END WHILE;

SELECT s_var AS message;
END
1 Answers
Related