sql久しぶりすぎて四苦八苦

もともとSQLではじょいんしてしゅとくしてとか
うにおんしてとか基本的なところをやった程度だった。
で、以前いろいろ調べて参考にさせてもらって作ったものはSSDの事故により紛失。

先日から作り直すことになった。
ほしい情報は
ID、ネストレベル、パス。
なんかうまくかけないし、三つに分かれちゃったけど、
親ID、IDな階層構造を持つテーブルから取得するストアドができたかな。

疲れているときのビールでふらふらしながらコードを書くのは面白い。

元はこちら。

--情報取得

CREATE PROC [dbo].[S_showHierarchy]
(
 @tableName nvarchar(256),
 @parentFieldName nvarchar(256),
 @childFieldName nvarchar(256),
 @rootId int,
 @idpath nvarchar(256) OUTPUT
)
AS
BEGIN
 SET NOCOUNT ON
 --テンポラリテーブル作成
 CREATE TABLE #hierarchy(id int NOT NULL,level int NOT NULL,idpath nvarchar(256))
 
 --呼び出し
 EXEC [dbo].[S_createHierarchyTable]
  @tableName = @tableName,
  @parentFieldName = @parentFieldName,
  @childFieldName = @childFieldName,
  @rootId = @rootId,
  @idpath = @idpath OUTPUT
 
 --最後に返却用にSELECTを実施
 SELECT * FROM #hierarchy
END


--テーブル作成

CREATE PROC [dbo].[S_createHierarchyTable]
(
 @tableName nvarchar(256),
 @parentFieldName nvarchar(256),
 @childFieldName nvarchar(256),
 @rootId int,
 @idpath nvarchar(256) OUTPUT
)
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @minID int, @whereToken nvarchar(1024)

 --指定親ID取得
 SET @whereToken = @childFieldName+' = '+STR(@rootId)
 EXEC dbo.S_getHierarchyMinID @tableName,@childFieldName,@whereToken,@minID OUTPUT
 --ネストパスの生成
 SET @idpath = @idpath + '-' + CONVERT(nvarchar,@minID)
 --PRINT REPLICATE('-', @@NESTLEVEL * 1) + STR(@minID) + STR(@@NESTLEVEL) + @idpath
 PRINT CONVERT(nvarchar,@minID) + '|' + CONVERT(nvarchar,@@NESTLEVEL) + '|' + @idpath
 
 --テンポラリテーブルに挿入
 INSERT INTO #hierarchy VALUES(@minID,@@NESTLEVEL,@idpath)
  
 --■次の階層の準備処理 --
 --指定親ID配下の最小値取得
 SET @whereToken = @parentFieldName+' = '+CONVERT(nvarchar,@rootId)
 EXEC dbo.S_getHierarchyMinID @tableName,@childFieldName,@whereToken,@minID OUTPUT
 --SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)
 
 WHILE @minID IS NOT NULL
 BEGIN
  --再帰
  EXEC dbo.S_createHierarchyTable @tableName,@parentFieldName,@childFieldName,@minID, @idpath
  SET @whereToken = @parentFieldName+' = '+CONVERT(nvarchar,@rootId)+' AND '+@childFieldName+' > '+CONVERT(nvarchar,@minID)
  EXEC dbo.S_getHierarchyMinID @tableName,@childFieldName,@whereToken,@minID OUTPUT
 END
 
END


--最小ID取得

CREATE PROC [dbo].[S_getHierarchyMinID]
(
 @tableName nvarchar(256),
 @childFieldName nvarchar(256),
 @whereToken nvarchar(256),
 @minID int OUTPUT
)
AS
DECLARE @SqlQuery nvarchar(1024)
SET @SqlQuery = '' + 
 'SELECT @minID = MIN('+@childFieldName+')' +
 ' FROM '+@tableName+
 ' WHERE '+@whereToken + ''
--PRINT @SqlQuery
EXECUTE SP_EXECUTESQL @SqlQuery,N'@minID int OUTPUT', @minID OUTPUT