Notes Made During Conversion of MS SQL Stored Procedures to MySQL Stored Procedures
Great online conversion tool can be found here:
http://www.sqlines.com/online
Below are selected statements in MySQL notation:
Convert
convert(int,DATEPART ( HH , NOW() ))
extract (unit from date)
convert(now(), time )
CONVERT(v_CondParam1, UNSIGNED INTEGER)
CAST(v_Value AS UNSIGNED INTEGER);
Exec Stored Procedure
call PostEvent (v_Value, ‘SATEL_CMD’);
Char Index
MySQL doesn’t have a built-in CHARINDEX() function. LOCATE() would be the MySQL equivalent.
Using SUBSTRING_INDEX() might be a more succinct way of doing this. Something like this (disclaimer: untested):
SUBSTRING_INDEX(POS, ‘,’, 1) for POS
SUBSTRING_INDEX(POS, ‘,’, -1) for POS2
select locate(‘r’,’qwerty’)
returns 4
!!! replace CHARINDEX with LOCATE
Date Format
DATE_FORMAT (EXEC_TIME_TO, ‘%H:%i:%s’)
update HS_DATA set VALUE = ‘yy’ , TIMESTAMP = NOW()
where ‘aa’ = ‘ALARM’ and NAME = ‘xx’;
select row_count();
Date Diff
if (TIMESTAMPDIFF(second,now(),v_TEMP_STATE_EXP) > 0)
WHILE
WHILE (v_i < 9)
DO
….
END WHILE;
Cursor
DECLARE HSScheduler_Cursor CURSOR FOR
SELECT ID,
EXEC_DAY_MO,
EXEC_DAY_TU,
EXEC_DAY_WE
FROM HS_SCHEDULER
WHERE STATUS = ‘true’;
OPEN HSScheduler_Cursor;
scheduler_cursor: LOOP
FETCH HSScheduler_Cursor INTO
v_Id,
v_Mo,
v_Tu,
v_We,
v_Th,
v_Fr,
END LOOP scheduler_cursor;
CLOSE HSScheduler_Cursor;
DECLARE v_rowCount int;
DECLARE v_loopCount int;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET @handler_invoked = 1;
END;
OPEN stsCursor;
set v_rowcount = found_rows();
set v_loopCount = 1;
— select v_rowCount;
main_loop: LOOP
— select v_loopCount , v_rowCount;
IF (v_loopCount > v_rowCount) THEN
LEAVE main_loop;
END IF;
FETCH stsCursor INTO v_WORKFLOW_ID……..
set v_loopCount = v_loopCount + 1;
END LOOP main_loop;
CLOSE stsCursor;
