Convert CTE Stored Procedure to Mysql Compatible Database Query

Question

My Category Table is

  TABLE [Category](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Title] [nvarchar](512) NULL,
        [Slug] [nvarchar](512) NULL,
        [Details] [text] NULL,
        [CategoryType] [int] NOT NULL,
        [ParentId] [int] NULL,
        [SortOrder] [int] NOT NULL
   )

And My CTE Stored Procedure is

CREATE PROCEDURE [dbo].[sp_AllCategoryPath]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 ;WITH CTE AS
 (
     SELECT Id, Title, Title as Path, ParentId, cast(Id as nvarchar(125)) as LinkPath,Slug
     FROM Category 
     WHERE ParentId IS NULL
     UNION ALL
     SELECT t.[Id], t.[Title], 
     cast(cte.[Path] +'/'+ t.Title as nvarchar(512)), 
     t.ParentId,
     cast(cte.[LinkPath]  +'/'+  CONVERT(varchar(512), t.Id) as nvarchar(125)),
     t.Slug
     FROM Category t
     INNER JOIN CTE ON t.ParentId = CTE.Id
 )

 SELECT cte.Id, cte.Title, cte.Path, cte.ParentId , c.Title as ParentName, LinkPath,cte.Slug
 FROM CTE cte LEFT JOIN Category c ON cte.ParentId = c.Id
 ORDER BY cte.Path

END

How can I convert this cte sp query to mysql sp compatible version ? I want to change my sp to query because I am changing my asp.net core application db provider MSSQL to MySql server. But I couldn't change cte sp to normal sp query. My MySql Server Version 5.1.73

It is a recursive CTE and starting from MySQL 8.0 it is natively supported. You should upgrade your MySQL — Lukasz Szozda, May 18 '19 at 11:07
Thank you @LukaszSzozda , but I can't change MySql version right now. — Yigit Tanriverdi, May 18 '19 at 11:22
Then your question is too broad. Please do some reading, make an effort at translating this query, and return here with specific issues. — Parfait, May 18 '19 at 13:41
Possible duplicate of [How to transform a MSSQL CTE query to MySQL?](https://stackoverflow.com/questions/8833535/how-to-transform-a-mssql-cte-query-to-mysql) — SMor, May 18 '19 at 13:43

score 0 · Answer 1 · answered May 18 '19 at 16:31

You can emulate RCTE behaviour with a MySQL flow control statements like WHILE, LOOP or REPEAT..UNTIL but all those statements are allowed only within the stored routines, functions, triggers and events. You can't use them in the plain queries like SELECT.

If you can use stored routines like CALL AllCatPath(...) with MySQL then you can implement recursion on your own. If stored routines are not allowed then you have to migrate to another implementation of the trees in the MySQL like "nested sets".

Convert CTE Stored Procedure to Mysql Compatible Database Query

1 Answers1