MySQL to MS SQL Conversion

[ssnaik84]

Hi Guys,

Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of things (stored procedures, functions).. we have to manually edit.
That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :)


=================================================

Index
-------------------------------------------------------
1. MySQL Variables
-- user variables (Global Variables)
-- local variables

2. Migration Hints
How to get
-- last inserted identity value
-- current date and time
-- declare a dummy table

3. MySQL String Functions
-- CONCAT
-- CONCAT_WS
-- GROUP_CONCAT

4. UNIQUEIDENTIFIER

5. How to return (exit) from SP conditionally?

6. How to execute Dynamic SQL in stored procedure?

7. MySQL Cursor

=================================================

1. MySQL Variables

In MySQL, there are 2 types of variables:

user variables: (Global Variables)
- symbol “@” is used to designate them.
- User variable is a variable, is working during the whole connection to the database.
- All variables for a given client connection are automatically freed when that client exits.
- User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients
- User variable names not case sensitive in MySQL 5.0 and up.
- Syntax: SET @var_name = expr [, @var_name = expr] …
- Example: SET @t1=0, @t2=0, @t3=0;
- Note: You can also assign a value to a user variable in statements other than SET.
In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements
e.g.

SET @t1=1, @t2=2, @t3=3;
SELECT @t1,@t2,@t3; ==> output: 1,2,3
SELECT @t1:=8,@t2,@t3; ==> output: 8,2,3
 

local variables: (Recommended in Stored Procedure)
- The DECLARE statement is used to declare local variables.
- local variable works only within a procedure body.
- DECLARE may only be used inside a BEGIN … END compound statement and must be at its start, before any other statements.
- The scope of a variable is within the BEGIN … END block.
- Syntax: DECLARE var_name [, var_name] … type [DEFAULT value]
- Example: DECLARE l_TagID INT DEFAULT 0;
- Note: If the DEFAULT clause is missing, the initial value is NULL.

How to use local variables in MySQL:
————————————-
To change Microsoft SQL Sever local variable name (@var) to MySQL local variable name, replace symbol “@” with “v_”. (select all, replace all)

Example: @userID ==> v_userID

————————————-

MS-SQL:
DECLARE @uname, @addr VARCHAR(200);
SELECT @uname = uname, @addr = address FROM tbluser WHERE uid = @uid;
 
MySQL:
DECLARE v_uname, v_addr VARCHAR(200);
SELECT uname, address INTO v_uname, v_addr tbluser FROM tbluser WHERE uid = v_uid;

————————————-

Example:

DELIMITER $$
DROP PROCEDURE IF EXISTS `mytest`$$
CREATE PROCEDURE `mytest`
(
IN v_id INT
)
BEGIN
create temporary table temp
(
id int primary key auto_increment ,
val int
);
insert into temp (val) values (22);
insert into temp (val) values (50);
insert into temp (id, val) values (99, 80);
insert into temp (val) values (9000);
begin
declare v_val int default 0;
select id, val into v_id, v_val from temp where id=v_id;
select v_id, v_val;
end;
– select v_id, v_val;
– (If you try to display local variables outof their scope (begin.. end), you will get error: Unknown column ‘v_val’ in ‘field list’)
drop temporary table temp;
END$$
DELIMITER ;
 
try to run it with: call mytest(100)

=================================================

2. Migration Hints


@@IDENTITY is also available in MySQL.

SELECT @@IDENTITY AS ‘Identity’;

it returns last inserted identity value.
But, better to use LAST_INSERT_ID()

—————————————–
How to update a table from other

UPDATE JOIN …..
ON . = .
SET . =
WHERE

—————————————–

MS-SQL: GetDate() ==> Use sysdate() , SELECT CURRENT_TIMESTAMP;

—————————————–

You are allowed to specify DUAL as a dummy table name in situations where no tables are referenced

SELECT 1 FROM dual
WHERE left(@sSQL,2) in ('v_','d_','e_','f_','g_','h_','i_','j_')
 

=================================================

3. MySQL String Functions


CONCAT function:
———————
- Always use CONCAT for concatenation (to replace “+” operator in MS-SQL).
- Avoid “||” (double pipe)
- Take care of NULL value with IFNULL(column_name, ”); before concatenation, Otherwise, it’ll return null.

Example:

DELIMITER $$
DROP PROCEDURE IF EXISTS `mytest`$$
CREATE PROCEDURE `mytest`()
BEGIN
declare v_str1, v_str2, v_str3, v_str4 varchar(20);
declare v_res varchar(100);
set v_str1 = “abc”;
set v_str2 = “pqr”;
set v_str3 = “xyz”;
set v_str4 = “123″;
set v_res = concat(v_str1,v_str2,v_str3, v_str4); — , null);
select v_res;
END$$
DELIMITER ;
RUN: call mytest();
 

CONCAT_WS function:
————————————

- for Concatenate With Separator and is a special form of CONCAT().
The first argument is the separator for the rest of the arguments.
The separator is added between the strings to be concatenated

- Advantage over CONCAT: need not to check IFNULL values of each column.
It takes cares of it internally.
- Note: If the separator is NULL, the result is NULL.

Syntax: CONCAT_WS(separator,str1,str2,…);

Example:

SELECT CONCAT_WS(’,’,’First name’,’Second name’,null,’Last Name’);
-> ‘First name,Second name,Last Name’

GROUP_CONCAT Function:
———————————————
-This function returns a string result with the concatenated non-NULL values from a group.
- USE: To concatenate multiple MySQL rows into one field

Example:
————-

if
select col_name from temp_table;
==>
col_name
———-
‘abc’
‘xyz’
‘pqr’
 
declare v_str varchar(100);
select group_concat( col_name separator ‘*’) into v_str from temp_table;
select v_str;
 
output: ‘abc*xyz*pqr’
 
 

Why to avoid “||” double pipe for concatenation
——————————————————–

double pipe can be used for concatenation, but, it is only legal while the sql mode is ansi.

You can tell mysqld to run in ANSI mode with the –ansi startup option.
mysqld –transaction-isolation=SERIALIZABLE –sql-mode=ANSI
or,
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = ‘ANSI’;

=================================================

4. How to replace UNIQUEIDENTIFIER

UNIQUEIDENTIFIER is not a data type in MySQL.
We can use VARCHAR(50), as both generate GUID.

MS-SQL:
——————-
NEWID()
output: D747AE09-5E4C-44BB-BCAE-DD40EDD7747B

MySQL :
——————-
UUID()
output: 28c93603-0752-102c-ab9e-93e0a9ae6d17

Example:
————————————–

DELIMITER $$
DROP PROCEDURE IF EXISTS `mytest`$$
CREATE PROCEDURE `mytest`()
BEGIN
DECLARE id VARCHAR(50);
SET id = UUID();
SELECT id;
END$$
DELIMITER ;
run it with: call mytest()
 

=================================================

5. How to return (exit) from SP conditionally?
—————————————————
There is no alternative keyword for “return” in SP of MS-SQL.
Use “LEAVE” instead.
Put extra BEGIN & END with a label (say main)
It’s also used, for exiting through a loop.

Example:

DELIMITER $$
DROP PROCEDURE IF EXISTS `mytest`$$
CREATE PROCEDURE `mytest`()
BEGIN
MAIN: BEGIN
SELECT ‘HI’;
IF 1=1 THEN
SELECT ‘ABC’;
LEAVE MAIN;
END IF;
SELECT ‘BYE’;
END MAIN;
END$$
DELIMITER ;
 
To Run: call mytest()
 

=================================================

6. How to execute Dynamic SQL in stored procedure

step 1. CONCATE it.
———————————————————-
CONCATE sql strings & store it into User Variable

SET @dynamic_sql = CONCATE(”SELECT * FROM mytable” , ” WHERE userid=”, v_uid);

step 2. PREPARE it.
———————————————————-
To execute dynamic sql, you need to prepare it first.

PREPARE complete_sql FROM @dynamic_sql;

Note: PREPARE statement does not work with local variables (e.g. v_sql).
It is explicitly designed to work with user variables and constant strings only.

step 3. EXECUTE it.
———————————————————-
EXECUTE complete_sql;

step 4. DEALLOCATE it.
———————————————————-
DEALLOCATE the prepared statement.

DEALLOCATE PREPARE complete_sql;


=================================================

7. MySQL Cursor

A cursor has following main steps:

1) DECLARE cursor-name CURSOR FOR SELECT …;
2) OPEN cursor-name;
3) FETCH cursor-name INTO variable [, variable];
4) CLOSE cursor-name;

———————————————————————
Example:

DELIMITER $$
DROP PROCEDURE IF EXISTS `newTest`$$
CREATE PROCEDURE `newTest`()
BEGIN
create temporary table temp
(
id int primary key auto_increment ,
val int
);
insert into temp (val) values (60);
insert into temp (val) values (60);
insert into temp (id, val) values (99, 80);
insert into temp (val) values (9000);
BEGIN
DECLARE colID, colVAL, handle INT;
DECLARE mycur CURSOR FOR SELECT id, val FROM temp; -- declare cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET handle = 1; -- declare handle
OPEN mycur; -- open cursor
FETCH mycur INTO colID, colVAL; -- fetch first row values from cursor
REPEAT
SELECT colID, colVAL; -- your code
FETCH mycur INTO colID, colVAL; -- fetch values from cursor
UNTIL handle = 1
END REPEAT;
CLOSE mycur; -- close cursor
END;
drop temporary table temp;
END$$
DELIMITER ;
 
call newTest();
 

[wizardry]

not to get off topic, better choice is mysql server!