I have trouble converting a stored procedure from Firebird to MySQL.
Here is my existing code from Firebird:
CREATE OR ALTER PROCEDURE GET_EOMONTH (
selectmonth integer,
selectyear integer)
returns (
lastdate timestamp)
as
declare variable vmonth integer;
declare variable vyear integer;
begin
VMONTH = selectmonth+1;
VYEAR = selectyear;
if (VMONTH=13) then
BEGIN
vmonth = 1;
vyear = selectyear + 1;
END
LASTDATE = CAST(VMONTH || '/1/' || VYEAR AS DATE) - 1;
/* Procedure Text */
suspend;
end^
Then I modify code above to:
CREATE PROCEDURE GET_EOMONTH (
selectmonth integer,
selectyear integer)
returns (
lastdate timestamp)
as
declare variable vmonth integer;
declare variable vyear integer;
begin
VMONTH = selectmonth+1;
VYEAR = selectyear;
if (VMONTH=13) then
BEGIN
vmonth = 1;
vyear = selectyear + 1;
END
LASTDATE = CAST(VMONTH || '/1/' || VYEAR AS DATE) - 1;
/* Procedure Text */
suspend;
end
From code above I got following errors like this:
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 '( lastdate timestamp) as declare variable vmonth integer' at line 4 */
--edited
I modified again in to this:
DELIMITER $$
CREATE PROCEDURE GET_EOMONTH (
IN selectmonth INT,
IN selectyear INT,
OUT LASTDATE VARCHAR(100))
BEGIN
DECLARE VMONTH INT;
DECLARE VYEAR INT;
SET VMONTH = selectmonth+1;
SET VYEAR = selectyear;
IF (VMONTH=13) THEN
BEGIN
SET VMONTH = 1;
SET VYEAR = selectyear + 1;
END;
END IF;
SET LASTDATE = CAST(VMONTH || '/1/' || VYEAR AS DATE) - 1;
SELECT @LASTDATE;
END
END$$
but, the error says:
SQL 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 'END' at line 19
Please advice.
--edited
Now I modify code like this:
DELIMITER $$
CREATE PROCEDURE GET_EOMONTH (
IN selectmonth INT,
IN selectyear INT,
OUT LASTDATE VARCHAR(100))
BEGIN
DECLARE VMONTH INT;
DECLARE VYEAR INT;
SET VMONTH = selectmonth+1;
SET VYEAR = selectyear;
IF (VMONTH=13) THEN
BEGIN
SET VMONTH = 1;
SET VYEAR = selectyear + 1;
END;
END IF;
SET LASTDATE = str_to_date(CONCAT(VYEAR, "-", VMONTH, "-1"), '%y/%m/%d') -1;
SELECT @LASTDATE;
END$$
DELIMITER ;
And it works.