0

I need to convert an existing MsSql stored procedure into mysql. Below mentioned query is a example stored procedure which I'm going to convert.

CREATE PROCEDURE [mydb].[mysp]
@input_userId nvarchar(255) 
AS 
BEGIN 
WITH tree(userId, managerUserId, name, Level) AS
(
SELECT t1.userId, t1.managerUserId, t1.name, 0
FROM UserData t1
WHERE managerUserId = @input_userId OR userId=@input_userId 
UNION ALL
SELECT t2.userId, t2.managerUserId, t2.name, t3.Level+1
FROM UserData t2
INNER JOIN tree t3 ON t2.managerUserId=t3.userId
) 

SELECT DISTINCT(res.name), res.userId, res.Level, t1.empNo
FROM tree AS res
INNER JOIN
UserData AS t1 ON res.userId = t1.userId
ORDER BY res.name asc

END

I'm confuse how to convert WITH tree part in to MySql. I tried to replace that with another select, but since that tree is using in other 2 select queries it didn't success. I'd be SO THANKFUL if you could support me to resolve this.

EDITED:

I tried below query in mysql 5.6.

CREATE PROCEDURE mydb.mysp(
IN input_userId VARCHAR(255)) 
 
BEGIN 
SELECT res.name, res.userId, res.Level, t1.empNo
FROM
(
SELECT userId, managerUserId, name, Level
FROM
(
SELECT t1.userId, t1.managerUserId, t1.name, 0
FROM UserData t1
WHERE managerUserId = @input_userId OR userId=@input_userId 
UNION ALL
SELECT t2.userId, t2.managerUserId, t2.name, t3.Level+1
FROM UserData t2
INNER JOIN tree t3 ON t2.managerUserId=t3.userId
) AS tree
)
FROM tree AS res
INNER JOIN
UserData AS t1 ON res.userId = t1.userId
ORDER BY res.name ASC

END

This is giving an error like,

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 'FROM tree AS res INNER JOIN UserData AS t1 ON res.userId = t1.userId ORDER BY' at line 20

koko
  • 169
  • 1
  • 13
  • 1
    If you are not using MySQL 8+, then you cannot easily do this. – Gordon Linoff Aug 12 '20 at 10:55
  • Unfortunately I'm using MySql 5.6. – koko Aug 12 '20 at 11:01
  • 1
    Then you need to convert the CTE into a derived table within the main select. – Shadow Aug 12 '20 at 11:55
  • @Shadow Tried so, but I'm getting an error as tree is used inside the main query. – koko Aug 12 '20 at 14:19
  • 1
    Include what you have tried and we can help to find a solution. – Shadow Aug 12 '20 at 14:35
  • @Shadow I Edited the question with the query I tried. What's the mistake I have done there? Could you please help? – koko Aug 12 '20 at 15:29
  • You need to have one big query with lots of subqueries as opposed to having two queries. You have a recursive CTE, which is not easy (nor particularly performant) to rewrite.See https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query for further help. – Shadow Aug 12 '20 at 16:26
  • @GordonLinoff What happened in MySQL 8 that makes this easier? – Orwellophile Oct 19 '21 at 05:00

0 Answers0