sqlserver树状结构表中,获取指定节点的所有父节点路径_MySQL

CREATE PROCEDURE [dbo].[A_P_GetParentIds]
    (
      @IdValue NVARCHAR(36) ,-- 子节点值
      @tableName NVARCHAR(MAX) ,-- 表名
      @ParentIdColumnName NVARCHAR(MAX) ,-- 父节点列名
      @ChildIdColumnName NVARCHAR(MAX)-- 子节点列名
	)
AS
    DECLARE @sqlTemp NVARCHAR(MAX); --查询sql
    SET @sqlTemp = &＃39; with cte_root as
(
 select &＃39; + @ParentIdColumnName + &＃39; from &＃39; + @tableName + &＃39; where &＃39;
        + @ChildIdColumnName + &＃39; = &＃39;&＃39;&＃39; + @IdValue + &＃39;&＃39;&＃39; union all
  select a.&＃39; + @ParentIdColumnName + &＃39; from &＃39; + @tableName
        + &＃39; a inner join cte_root b on a.&＃39; + @ChildIdColumnName + &＃39;=b.&＃39;
        + @ParentIdColumnName + &＃39; ) &＃39; + CHAR(10) + &＃39;select * from cte_root &＃39;; 

------返回查询结果-----
    EXEC sp_executesql @sqlTemp;


GO

CREATE PROCEDURE [dbo].[A_P_GetParentInfo]
    (
      @IdValue NVARCHAR(36) ,-- 子节点值
      @tableName NVARCHAR(MAX) ,-- 表名
      @ParentIdColumnName NVARCHAR(MAX) ,-- 父节点列名
      @ChildIdColumnName NVARCHAR(MAX)-- 子节点列名
	)
AS
    DECLARE @sqlTemp NVARCHAR(MAX); --查询sql
    SET @sqlTemp = &＃39; with cte_root as
(
 select *  from &＃39; + @tableName + &＃39; where &＃39;
        + @ChildIdColumnName + &＃39; = &＃39;&＃39;&＃39; + @IdValue + &＃39;&＃39;&＃39; union all
  select a.*  from &＃39; + @tableName
        + &＃39; a inner join cte_root b on a.&＃39; + @ChildIdColumnName + &＃39;=b.&＃39;
        + @ParentIdColumnName + &＃39; ) &＃39; + CHAR(10) + &＃39;select * from cte_root &＃39;; 

------返回查询结果-----
    EXEC sp_executesql @sqlTemp;

GO

调用测试：

DECLARE	@return_value int

EXEC	@return_value = [dbo].[A_P_GetParentIds]
		@IdValue = N&＃39;a29672f9-3d03-4cfc-9cb0-403ce729db5b&＃39;,--子节点值
		@tableName = N&＃39;SystemMenu&＃39;,--表名
		@ParentIdColumnName = N&＃39;ParentId&＃39;,--父节点列名
		@ChildIdColumnName = N&＃39;Id&＃39;--子节点列名

SELECT	&＃39;Return Value&＃39; = @return_value

GO