将MySQL存储过程迁移到sql server 2012 [英] Migrate MySQL store procedure into sql server 2012

问题描述

我已经迁移了一个存储过程，但是在迁移SP后，由于某些MySQL函数，它会带有注释行。

在MySQL中我使用FIND_IN_SET（）函数但是这个函数不适合sqlserver这就是为什么行自动显示注释。

i需要转换/将MySQL存储过程迁移到sqlserver。



：-MySQL SP

 DELIMITER $$ 
 
  DROP   PROCEDURE   IF   EXISTS `UserPreferences_GetAllName` $$ 
  CREATE  DEFINER =`root` @`localhost`  PROCEDURE `UserPreferences_GetAllName`（）
  BEGIN  
 
 选择 d.UserId，d.CreatorId，
 max（ case  当 col = <时span class =code-string>'  JOB' 然后 d.RecordValue  end ） as  JOB，
 max（ case  当 col = '  REMOTEACCESSTO'   d.RecordValue  end ） as  REMOTEACCESSTO ，
 max（ case  当 col = '   FLOWDIAGRAM' 然后 d.RecordValue  end ） as  FLOWDIAGRAM，
 max（ case  当 col = '  PAGESIZE' 然后 d.RecordValue  end ） as  PAGESIZE 
 来自 
（
 选择 a .Id，a.CreatorId，a.UserId，'  JOB' col， case  何时（a.RecordValue = '  All'）  a.RecordValue 
  else  GROUP_CONCAT（  DISTINCT  c.Jobname） end   as  RecordValue 
 来自 userpreference a  left   join 工作c 
  ON  FIND_IN_SET（c.id，REPLACE（a.Rec） ordValue，' ：'，' < span class =code-string>，'））>  0  
 其中 a.RecDescription = '  JOB' 和 a.IsActive = 1 
  GROUP   BY  a.Id，a.UserId 
  union  
 选择 a.Id，a.CreatorId，a.UserId，'  REMOTEACCESSTO' col， case  何时（a.RecordValue = ' 所有'）然后 a.RecordValue 
  else  GROUP_CONCAT（ DISTINCT  s.Name） end   as  RecordValue 
 来自 userpref erence a  left   join  server s 
  / *   ON FIND_IN_SET（s.id，REPLACE（a.RecordValue，'：'，'，'））> 0 * /  
  ON  s.id  IN （REPLACE（a.RecordValue） ，' ：'，' ，'））>  0  
 其中 a.RecDescription = '  REMOTEACCESSTO' 和 a.IsActive = 1 
  GROUP   BY  a.Id，a.UserId 
  union  
  SELECT  a.Id，a.CreatorId，a.UserId，'  FLOWDIAGRAM' col，（ case  何时（a.RecordValue = < span class =code-string>'  1'）然后  可见' 
 其他  不可见' 
  end ） as  RecordValue 
  FROM  userpreference a 
 其中 RecDescription = ' 流程图 和 a.IsActive = 1 
  GROUP   BY  a.Id，a.Userid 
  union  
  SELECT  a.Id，a.CreatorId，a.UserId，< span class =code-string>'  PAGESIZE' col，a.RecordValue 
  FROM  userpreference a 
 其中 RecDescription = '  PAGE SIZE' 和 a.IsActive = 1  GROUP   BY  a.id，a.UserId 
）d  Group  < span class =code-keyword> by  d.UserId; 
 
 
  END  $$ 
 
 DELIMITER; 
 
 
 
 //转换后 显示  below：
 
 
 
 
  USE  [opscentral] 
  GO  
  / *   *****对象：StoredProcedure [ dbo]。[UserPreferences_GetAllName]脚本日期：10-10-2015 14:13:31 ****** /  
  SET  < span class =code-sdkkeyword> ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
  / *   
 * SSMA信息性消息：
 * M2SS0003：转换期间忽略了以下SQL子句：
 * DEFINER =`root` @`loca lhost`。 
 * /  
 
  / *   
 * SSMA信息性消息：
 * M2SS0134：不支持以下注释的转换：ON FIND_IN_SET（s.id，REPLACE（a.RecordValue，'：'，'，'））> 0 
 * 
 * /  
 
  ALTER  程序 [dbo]。[UserPreferences_GetAllName] 
  AS  
  BEGIN  
 
  SET   XACT_ABORT   ON  
 
  SET   NOCOUNT   ON  
 
  / *   
 * SSMA错误消息：
 * M2SS0135：未分析的SQL [select d.UserId，d.CreatorId，
 * max（col ='JOB'然后d.RecordValue结束时的情况）为JOB，
 * max（col时的情况） ='REMOTEACCESSTO'然后d.RecordValue结束）作为REMOTEACCESSTO，
 * max（当col ='FLOWDIAGRAM'然后d.RecordValue结束时的情况）为FLOWDIAGRAM，
 * max（案例w hen col ='PAGESIZE'，然后d.RecordValue结束）作为PAGESIZE 
 *来自
 *（
 *选择a.Id，a.CreatorId，a.UserId，'JOB'col，case when（a.RecordValue ='All'）然后a.RecordValue 
 * else GROUP_CONCAT（DISTINCT c.Jobname）从用户首选项结束为RecordValue 
 *左联接作业c 
 * ON FIND_IN_SET（ c.id，REPLACE（a.RecordValue，'：'，'，'））> 0 
 *其中a.RecDescription ='JOB'和a.IsActive = 1 
 * GROUP BY a.Id，a.UserId 
 * union 
 * select a.Id， a.CreatorId，a.UserId，'REMOTEACCESSTO'col，case（a.RecordValue ='All'）然后a.RecordValue 
 * else GROUP_CONCAT（DISTINCT s.Name）以RecordValue 
 *结尾userpreference a left join server s 
 * / * ON FIND_IN_SET（s.id，REPLACE（a.RecordValue，'：'，'，'））> 0 * / 
 * ON s.id IN（REPLACE（a.RecordValue，'：'，'，'））> 0 
 *其中a.RecDescription ='REMOTEACCESSTO'和a.IsActive = 1 
 * GROUP BY a.Id，a.UserId 
 * union 
 * SELECT a.Id， a.CreatorId，a.UserId，'FLOWDIAGRAM'col，（例如当（a.RecordValue ='1'）然后'可见'
 *否''不可见'
 *结束时）作为RecordValue 
 * FROM userpreference a 
 *其中RecDescription ='FLOW DIAGRAM'和a.IsActive = 1 
 * GROUP BY a.Id，a.Userid 
 * union 
 * SELECT a.Id，a.CreatorId，a.UserId，'PAGESIZE'col，a.RecordValue 
 * FROM userpreference a 
 *其中RecDescription ='PAGE SIZE'和a.IsActive = 1 GROUP BY a。 id，a.UserId 
 *）d by d.UserId;]无法转换。 
 
 * /  
 
 
 
  END  

解决方案

DROP 程序 < span class =code-keyword> IF EXISTS `UserPreferences_GetAllName`

CREATE DEFINER =`root` @`localhost` PROCEDURE `UserPreferences_GetAllName`（）
BEGIN

选择 d.UserId，d.CreatorId，
max（案例 当 col = ' JOB' 然后 d.RecordValue end ） as JOB，
max（ case when col = ' REMOTEACCESSTO' 然后 d.RecordValue end ） as REMOTEACCESSTO，
max（ case 当 col = ' FLOWDIAGRAM' 然后 d.RecordValue end ） as FLOWDIAGRAM，
max（ case col = ' PAGESIZE' d.RecordValue < span class =code-keyword> end ） as PAGESIZE
来自
（
选择 a.Id，a.CreatorId，a。 UserId，' JOB' col， case 时（a.RecordValue = ' All'）然后 a.RecordValue
else GROUP_CONCAT（ DISTINCT c.Jobname） end as RecordValue
from userpreference a left join jobs c
ON FIND_IN_SET（c.id，REPLACE（a.RecordValue，' ：'，' ，'））> 0
其中 a.RecDescription = ' JOB' 和 a.IsActive = 1
GROUP BY a.Id，a.UserId
union
选择 a.Id，a.CreatorId，a.UserId，' REMOTEACCESSTO' col， case 何时（a.RecordValue = ' 所有'）然后 a.RecordValue
else GROUP_CONCAT（ DISTINCT s.Name） end as RecordValue
来自 userpref erence a left join server s
/ * ON FIND_IN_SET（s.id，REPLACE（a.RecordValue，'：'，'，'））> 0 * /
ON s.id IN （REPLACE（a.RecordValue） ，' ：'，' ，'））> 0
其中 a.RecDescription = ' REMOTEACCESSTO' 和 a.IsActive = 1
GROUP BY a.Id，a.UserId
union
SELECT a.Id，a.CreatorId，a.UserId，' FLOWDIAGRAM' col，（ case 何时（a.RecordValue = < span class =code-string>' 1'）然后 可见'
其他 不可见'
end ） as RecordValue
FROM userpreference a
其中 RecDescription = ' 流程图 和 a.IsActive = 1
GROUP BY a.Id，a.Userid
union
SELECT a.Id，a.CreatorId，a.UserId，< span class =code-string>' PAGESIZE' col，a.RecordValue
FROM userpreference a
其中 RecDescription = ' PAGE SIZE' 和 a.IsActive = 1 GROUP BY a.id，a.UserId
）d Group < span class =code-keyword> by d.UserId;


END

DELIMITER;



//转换后 显示 below：




USE [opscentral]
GO
/ * *****对象：StoredProcedure [ dbo]。[UserPreferences_GetAllName]脚本日期：10-10-2015 14:13:31 ****** /
SET < span class =code-sdkkeyword> ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/ *
* SSMA信息性消息：
* M2SS0003：转换期间忽略了以下SQL子句：
* DEFINER =`root` @`loca lhost`。
* /

/ *
* SSMA信息性消息：
* M2SS0134：不支持以下注释的转换：ON FIND_IN_SET（s.id，REPLACE（a.RecordValue，'：'，'，'））> 0
*
* /

ALTER 程序 [dbo]。[UserPreferences_GetAllName]
AS
BEGIN

SET XACT_ABORT ON

SET NOCOUNT ON

/ *
* SSMA错误消息：
* M2SS0135：未分析的SQL [select d.UserId，d.CreatorId，
* max（col ='JOB'然后d.RecordValue结束时的情况）为JOB，
* max（col时的情况） ='REMOTEACCESSTO'然后d.RecordValue结束）作为REMOTEACCESSTO，
* max（当col ='FLOWDIAGRAM'然后d.RecordValue结束时的情况）为FLOWDIAGRAM，
* max（案例w hen col ='PAGESIZE'，然后d.RecordValue结束）作为PAGESIZE
*来自
*（
*选择a.Id，a.CreatorId，a.UserId，'JOB'col，case when（a.RecordValue ='All'）然后a.RecordValue
* else GROUP_CONCAT（DISTINCT c.Jobname）从用户首选项结束为RecordValue
*左联接作业c
* ON FIND_IN_SET（ c.id，REPLACE（a.RecordValue，'：'，'，'））> 0
*其中a.RecDescription ='JOB'和a.IsActive = 1
* GROUP BY a.Id，a.UserId
* union
* select a.Id， a.CreatorId，a.UserId，'REMOTEACCESSTO'col，case（a.RecordValue ='All'）然后a.RecordValue
* else GROUP_CONCAT（DISTINCT s.Name）以RecordValue
*结尾userpreference a left join server s
* / * ON FIND_IN_SET（s.id，REPLACE（a.RecordValue，'：'，'，'））> 0 * /
* ON s.id IN（REPLACE（a.RecordValue，'：'，'，'））> 0
*其中a.RecDescription ='REMOTEACCESSTO'和a.IsActive = 1
* GROUP BY a.Id，a.UserId
* union
* SELECT a.Id， a.CreatorId，a.UserId，'FLOWDIAGRAM'col，（例如当（a.RecordValue ='1'）然后'可见'
*否''不可见'
*结束时）作为RecordValue
* FROM userpreference a
*其中RecDescription ='FLOW DIAGRAM'和a.IsActive = 1
* GROUP BY a.Id，a.Userid
* union
* SELECT a.Id，a.CreatorId，a.UserId，'PAGESIZE'col，a.RecordValue
* FROM userpreference a
*其中RecDescription ='PAGE SIZE'和a.IsActive = 1 GROUP BY a。 id，a.UserId
*）d by d.UserId;]无法转换。

* /



END

I have migrated one stored procedure but after migrated SP it is coming with commented lines because of some MySQL functions.
In MySQL i am using FIND_IN_SET() function but this function is not suitable in sqlserver that's why lines automatically showing commented.
i need to convert/Migrate MySQL stored procedure into sqlserver.

:-MySQL SP

DELIMITER $$

DROP PROCEDURE IF EXISTS `UserPreferences_GetAllName` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UserPreferences_GetAllName`()
BEGIN

select d.UserId,d.CreatorId,
  max(case when col = 'JOB' then d.RecordValue end) as JOB,
  max(case when col = 'REMOTEACCESSTO' then d.RecordValue end) as REMOTEACCESSTO,
  max(case when col = 'FLOWDIAGRAM' then d.RecordValue end) as FLOWDIAGRAM,
  max(case when col = 'PAGESIZE' then d.RecordValue end) as PAGESIZE
from
(
  select a.Id,a.CreatorId, a.UserId, 'JOB' col, case when (a.RecordValue ='All') then a.RecordValue
else GROUP_CONCAT(DISTINCT c.Jobname) end as RecordValue
  from userpreference a left join jobs c
  ON FIND_IN_SET(c.id, REPLACE(a.RecordValue, ':', ',')) > 0
  where a.RecDescription='JOB' and a.IsActive=1
  GROUP BY a.Id, a.UserId
  union
  select a.Id,a.CreatorId, a.UserId, 'REMOTEACCESSTO' col, case when (a.RecordValue ='All') then a.RecordValue
else GROUP_CONCAT(DISTINCT s.Name) end as RecordValue
  from userpreference a left join server s
  /*ON FIND_IN_SET(s.id, REPLACE(a.RecordValue, ':', ',')) > 0 */
  ON s.id IN (REPLACE(a.RecordValue, ':', ',')) > 0
  where a.RecDescription='REMOTEACCESSTO' and a.IsActive=1
  GROUP BY a.Id, a.UserId
  union
  SELECT a.Id,a.CreatorId, a.UserId, 'FLOWDIAGRAM' col,  (case when (a.RecordValue ='1') then 'Visible'
else 'Not Visible'
  end) as RecordValue
FROM userpreference a
where RecDescription='FLOW DIAGRAM' and a.IsActive=1
GROUP BY a.Id,a.Userid
  union
  SELECT a.Id,a.CreatorId, a.UserId, 'PAGESIZE' col, a.RecordValue
FROM userpreference a
where RecDescription='PAGE SIZE'  and a.IsActive=1 GROUP BY a.id,a.UserId
) d Group by d.UserId;


END $$

DELIMITER ;



//After converted it is showing as below:




USE [opscentral]
GO
/****** Object:  StoredProcedure [dbo].[UserPreferences_GetAllName]    Script Date: 10-10-2015 14:13:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
*   SSMA informational messages:
*   M2SS0003: The following SQL clause was ignored during conversion:
*   DEFINER = `root`@`localhost`.
*/

/*
*   SSMA informational messages:
*   M2SS0134: Conversion of following Comment(s) is not supported : ON FIND_IN_SET(s.id, REPLACE(a.RecordValue, ':', ',')) > 0 
*
*/

ALTER PROCEDURE [dbo].[UserPreferences_GetAllName]
AS 
   BEGIN

      SET  XACT_ABORT  ON

      SET  NOCOUNT  ON

      /* 
      *   SSMA error messages:
      *   M2SS0135: Unparsed SQL [select d.UserId,d.CreatorId,
      *     max(case when col = 'JOB' then d.RecordValue end) as JOB,
      *     max(case when col = 'REMOTEACCESSTO' then d.RecordValue end) as REMOTEACCESSTO,
      *     max(case when col = 'FLOWDIAGRAM' then d.RecordValue end) as FLOWDIAGRAM,
      *     max(case when col = 'PAGESIZE' then d.RecordValue end) as PAGESIZE
      *   from
      *   (
      *     select a.Id,a.CreatorId, a.UserId, 'JOB' col, case when (a.RecordValue ='All') then a.RecordValue
      *   else GROUP_CONCAT(DISTINCT c.Jobname) end as RecordValue
      *     from userpreference a left join jobs c
      *     ON FIND_IN_SET(c.id, REPLACE(a.RecordValue, ':', ',')) > 0
      *     where a.RecDescription='JOB' and a.IsActive=1
      *     GROUP BY a.Id, a.UserId
      *     union
      *     select a.Id,a.CreatorId, a.UserId, 'REMOTEACCESSTO' col, case when (a.RecordValue ='All') then a.RecordValue
      *   else GROUP_CONCAT(DISTINCT s.Name) end as RecordValue
      *     from userpreference a left join server s
      *     / *ON FIND_IN_SET(s.id, REPLACE(a.RecordValue, ':', ',')) > 0 * / 
      *     ON s.id IN (REPLACE(a.RecordValue, ':', ',')) > 0
      *     where a.RecDescription='REMOTEACCESSTO' and a.IsActive=1
      *     GROUP BY a.Id, a.UserId
      *     union
      *     SELECT a.Id,a.CreatorId, a.UserId, 'FLOWDIAGRAM' col,  (case when (a.RecordValue ='1') then 'Visible'
      *   else 'Not Visible'
      *     end) as RecordValue
      *   FROM userpreference a
      *   where RecDescription='FLOW DIAGRAM' and a.IsActive=1
      *   GROUP BY a.Id,a.Userid
      *     union
      *     SELECT a.Id,a.CreatorId, a.UserId, 'PAGESIZE' col, a.RecordValue
      *   FROM userpreference a
      *   where RecDescription='PAGE SIZE'  and a.IsActive=1 GROUP BY a.id,a.UserId
      *   ) d Group by d.UserId;] cannot be converted.

      */



   END

解决方案

DROP PROCEDURE IF EXISTS `UserPreferences_GetAllName`

CREATE DEFINER=`root`@`localhost` PROCEDURE `UserPreferences_GetAllName`() BEGIN select d.UserId,d.CreatorId, max(case when col = 'JOB' then d.RecordValue end) as JOB, max(case when col = 'REMOTEACCESSTO' then d.RecordValue end) as REMOTEACCESSTO, max(case when col = 'FLOWDIAGRAM' then d.RecordValue end) as FLOWDIAGRAM, max(case when col = 'PAGESIZE' then d.RecordValue end) as PAGESIZE from ( select a.Id,a.CreatorId, a.UserId, 'JOB' col, case when (a.RecordValue ='All') then a.RecordValue else GROUP_CONCAT(DISTINCT c.Jobname) end as RecordValue from userpreference a left join jobs c ON FIND_IN_SET(c.id, REPLACE(a.RecordValue, ':', ',')) > 0 where a.RecDescription='JOB' and a.IsActive=1 GROUP BY a.Id, a.UserId union select a.Id,a.CreatorId, a.UserId, 'REMOTEACCESSTO' col, case when (a.RecordValue ='All') then a.RecordValue else GROUP_CONCAT(DISTINCT s.Name) end as RecordValue from userpreference a left join server s /*ON FIND_IN_SET(s.id, REPLACE(a.RecordValue, ':', ',')) > 0 */ ON s.id IN (REPLACE(a.RecordValue, ':', ',')) > 0 where a.RecDescription='REMOTEACCESSTO' and a.IsActive=1 GROUP BY a.Id, a.UserId union SELECT a.Id,a.CreatorId, a.UserId, 'FLOWDIAGRAM' col, (case when (a.RecordValue ='1') then 'Visible' else 'Not Visible' end) as RecordValue FROM userpreference a where RecDescription='FLOW DIAGRAM' and a.IsActive=1 GROUP BY a.Id,a.Userid union SELECT a.Id,a.CreatorId, a.UserId, 'PAGESIZE' col, a.RecordValue FROM userpreference a where RecDescription='PAGE SIZE' and a.IsActive=1 GROUP BY a.id,a.UserId ) d Group by d.UserId; END

DELIMITER ; //After converted it is showing as below: USE [opscentral] GO /****** Object: StoredProcedure [dbo].[UserPreferences_GetAllName] Script Date: 10-10-2015 14:13:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* * SSMA informational messages: * M2SS0003: The following SQL clause was ignored during conversion: * DEFINER = `root`@`localhost`. */ /* * SSMA informational messages: * M2SS0134: Conversion of following Comment(s) is not supported : ON FIND_IN_SET(s.id, REPLACE(a.RecordValue, ':', ',')) > 0 * */ ALTER PROCEDURE [dbo].[UserPreferences_GetAllName] AS BEGIN SET XACT_ABORT ON SET NOCOUNT ON /* * SSMA error messages: * M2SS0135: Unparsed SQL [select d.UserId,d.CreatorId, * max(case when col = 'JOB' then d.RecordValue end) as JOB, * max(case when col = 'REMOTEACCESSTO' then d.RecordValue end) as REMOTEACCESSTO, * max(case when col = 'FLOWDIAGRAM' then d.RecordValue end) as FLOWDIAGRAM, * max(case when col = 'PAGESIZE' then d.RecordValue end) as PAGESIZE * from * ( * select a.Id,a.CreatorId, a.UserId, 'JOB' col, case when (a.RecordValue ='All') then a.RecordValue * else GROUP_CONCAT(DISTINCT c.Jobname) end as RecordValue * from userpreference a left join jobs c * ON FIND_IN_SET(c.id, REPLACE(a.RecordValue, ':', ',')) > 0 * where a.RecDescription='JOB' and a.IsActive=1 * GROUP BY a.Id, a.UserId * union * select a.Id,a.CreatorId, a.UserId, 'REMOTEACCESSTO' col, case when (a.RecordValue ='All') then a.RecordValue * else GROUP_CONCAT(DISTINCT s.Name) end as RecordValue * from userpreference a left join server s * / *ON FIND_IN_SET(s.id, REPLACE(a.RecordValue, ':', ',')) > 0 * / * ON s.id IN (REPLACE(a.RecordValue, ':', ',')) > 0 * where a.RecDescription='REMOTEACCESSTO' and a.IsActive=1 * GROUP BY a.Id, a.UserId * union * SELECT a.Id,a.CreatorId, a.UserId, 'FLOWDIAGRAM' col, (case when (a.RecordValue ='1') then 'Visible' * else 'Not Visible' * end) as RecordValue * FROM userpreference a * where RecDescription='FLOW DIAGRAM' and a.IsActive=1 * GROUP BY a.Id,a.Userid * union * SELECT a.Id,a.CreatorId, a.UserId, 'PAGESIZE' col, a.RecordValue * FROM userpreference a * where RecDescription='PAGE SIZE' and a.IsActive=1 GROUP BY a.id,a.UserId * ) d Group by d.UserId;] cannot be converted. */ END

这篇关于将MySQL存储过程迁移到sql server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！