实例1-gettree 和分页

-- zx
create PROCEDURE getbomtree
@MaterialID INT --参数,父节点的id
AS
BEGIN

-- 如果主BOM禁用,不显示树结构
IF NOT EXISTS( SELECT * FROM dbo.M_BD$$BomMain WHERE MaterialID=@MaterialID AND IsDisabled=1)
BEGIN
RETURN 
END

DECLARE @Levels int
CREATE TABLE #temp_BomDetail(
S_ID INT ,
S_PID INT
)
DECLARE @Tree TABLE
( uid int identity(1,1),
S_ID INT null,
S_PID int null,
SS_NAME varchar(200) NULL,
Levels INT
)
INSERT INTO #temp_BomDetail
SELECT BomDetail.MaterialID AS S_ID ,BomMain.MaterialID AS S_PID 
FROM dbo.M_BD$$BomDetail AS BomDetail
LEFT JOIN dbo.M_BD$$BomMain AS BomMain ON BomDetail.BOMMainID=BomMain.uid

--插入0,顶层节点
INSERT INTO @Tree(S_ID,S_PID,Levels)
SELECT MaterialID,0,0 FROM dbo.M_BD$$BomMain WHERE MaterialID=@MaterialID

--插入1级节点
INSERT INTO @Tree(S_ID,S_PID,Levels)
SELECT S_ID ,S_PID,1 
FROM #temp_BomDetail
WHERE S_PID=@MaterialID

--插入2级以后节点,判断树的最后插入的节点是否还有子节点
SET @Levels=1    
WHILE EXISTS( SELECT S_ID FROM #temp_BomDetail WHERE S_PID IN(SELECT S_ID FROM @Tree WHERE Levels=@Levels))
BEGIN
--树中的最后一级 S_ID 作为父级 
INSERT @Tree(S_ID,S_PID,Levels) 
SELECT S_ID,S_PID,@Levels+1 FROM #temp_BomDetail WHERE S_PID IN(SELECT S_ID FROM @Tree WHERE Levels=@Levels)    
SET @Levels+=1;
END

-- 循环结束，删除节点
DROP table #temp_BomDetail

--更新物料名称
UPDATE @Tree SET SS_NAME=Name+' '+CodeNumber FROM @Tree t1 
LEFT JOIN dbo.P_BD$$Material AS Material ON Material.uid=t1.S_ID --直接用 Material.uid=@tree.S_ID 会报错误，必须把@tree 重命名为 t1

SELECT * FROM @Tree END

  

 分页

ALTER PROC Pagination
(
@limit INT,
@page INT,
@sqlstr VARCHAR(max)
)
AS
BEGIN
DECLARE @sqlStr_ VARCHAR(max)

SET @sqlStr_='SELECT TOP'+CONVERT(VARCHAR(10),@limit) + ' * FROM ('+@sqlstr+') t where rownumber>'+CONVERT(VARCHAR(10),(@page-1)*@limit) 
EXEC(@sqlStr_)
END