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