Converting MS T/SQL to MySQL

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;

Advertisements