34,590
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE TB
(
ID INT PRIMARY KEY, --节点ID
CPNAME NVARCHAR(50),
TS VARCHAR(50),
TN INT, --父节点ID
TJ INT,
CHECK(ID<>TN)
)
ALTER PROC USP_INSERTTEMP
@ID INT,
@CPNAME NVARCHAR(50),
@TS VARCHAR(50),
@TN VARCHAR(50)
AS
BEGIN
IF @TN='0'
BEGIN
INSERT INTO TB
SELECT @ID,@CPNAME+'|',@TS+'|',@TN,1
END
ELSE
BEGIN
IF NOT EXISTS(SELECT 1 FROM TB WHERE ID=@TN)
PRINT '表中不存在父节点'+@TN+''
INSERT INTO TB
SELECT @ID,CPNAME+@CPNAME+'|',TS+@TS+'|',@TN,TJ+1 FROM TB
WHERE ID=@TN
END
END
USP_INSERTTEMP 1,'农林牧副渔','1',0
USP_INSERTTEMP 2,'农业机械','2',1
USP_INSERTTEMP 3,'农业机械','2',2
SELECT * FROM TB
DROP TABLE TB