SQL Error:# 1064 when creating function in plsql using mysql

Question

Here is my plsql code

CREATE FUNCTION calculate_hours(mon varchar(20),years INT,i INT)
RETURNS varchar(10)
READS SQL DATA
BEGIN
DECLARE col1 varchar(10);
DECLARE col2 varchar(10);
DECLARE str1 varchar(30);
DECLARE str2 varchar(30);
SET str1='CHR_MORNING'+i;
SET str2='CHR_EVENING'+i;
SELECT str1 ,str2   FROM att_t_register WHERE CHR_MONTH=mon AND INT_YEAR=years;
SET col1=str1;
SET col2=str2;
IF(col1='SUN' && col2='SUN') THEN
RETURN 'S';
ELSE IF(col1='HOL' && col2='HOL') THEN
RETURN 'H';
ELSE IF(col1='LEA' && col2='LEA') THEN
RETURN 'L';
ELSE IF(col1='ABS' && col2='ABS') THEN
RETURN '-';
ELSE IF(col1='NA' && col2='NA') THEN
RETURN '-';
END IF;
RETURN '8';
END;

When I was running this I got error like..

SQL EXECUTION ERROR # 1064. RESPONSE FROM DATA BASE 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 " at line 26

please help me where I did mistake.

Answer 1

You don't need a function for this, you need a stored procedure.

DELIMITER $$
CREATE PROCEDURE proc_name(IN col_number1 INT, IN col_number2 INT)
BEGIN
SET @col1 := CONCAT('CHR_MORNING', col_number1);
SET @col2 := CONCAT('CHR_EVENING', col_number2);

SET @sql := CONCAT('
SELECT 
CASE WHEN ', @col1, '="SUN" && ', @col2, '="SUN" THEN "S"
WHEN (', @col1, '="HOL" && ', @col2, '="HOL") THEN "H"
WHEN (', @col1, '="LEA" && ', @col2, '="LEA") THEN "L"
WHEN (', @col1, '="ABS" && ', @col2, '="ABS") THEN "-"
WHEN (', @col1, '="NA" && ', @col2, '="NA") THEN "-"
ELSE "8"
END AS whatever_you_want_to_name_your_column
FROM att_t_register WHERE CHR_MONTH = "jan" AND INT_YEAR = 2014;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

Then you'd execute it with

CALL proc_name(1, 1);