这是我的3个表结构
表名XType
XYNum 自动ID
SFCNum 设备类型ID
XYNO 序号
XYName 设备巡视分类名称表名XItem
XINum ID
XYNum 分类ID
XINO 序号
XItem 巡检项目
XIType 巡检类型,1为状态项,0为数据项
XIright 数据正常值
XIDroplist 缺陷描述待选项表名XContent
XCNum ID
XINum 巡检项ID
XCNO 序号
XContent 缺陷描述
XCLevel 缺陷等级(1,2,3)
表名XType
XYNum 自动ID
SFCNum 设备类型ID
XYNO 序号
XYName 设备巡视分类名称表名XItem
XINum ID
XYNum 分类ID
XINO 序号
XItem 巡检项目
XIType 巡检类型,1为状态项,0为数据项
XIright 数据正常值
XIDroplist 缺陷描述待选项表名XContent
XCNum ID
XINum 巡检项ID
XCNO 序号
XContent 缺陷描述
XCLevel 缺陷等级(1,2,3)
搂主想实现什么?
===>
while exists(select 1 from dbo.XItem where XYNum = @intXYNumOne)
如果2层是没问题 主要是3层
2层的这样没问题
CREATE PROCEDURE [dbo].[Pr_XItem_Copy](@intXYNumOne INT=0,@intXYNumTwo INT=0)
WITH RECOMPILE
AS
delete from dbo.XItem where XYNum = @intXYNumTwo;
insert into dbo.XItem(XYNum,XINO,XItem,XIType,XIright)
select @intXYNumTwo,XINO,XItem,XIType,XIright
from dbo.XItem
where XYNum = @intXYNumOne;
完整的写法
case '某某' when '某某' then '某某' else '某某' end
while exists--加上这个
while exists(select @intXINumOne = XINum from dbo.XItem where XYNum = @intXYNumOne)
消息 102,级别 15,状态 1,过程 Pr_XItem_Copy,第 11 行
'=' 附近有语法错误。
WITH RECOMPILE
AS
declare @intXINumOne INT
declare @intXINumTwo INT;
delete from dbo.XItem where XYNum = @intXYNumTwo;
insert into dbo.XItem(XYNum,XINO,XItem,XIType,XIright)
select @intXYNumTwo,XINO,XItem,XIType,XIright
from dbo.XItem
where XYNum = @intXYNumOne;
while exists(select @intXINumOne = XINum from dbo.XItem where XYNum = @intXYNumOne)
begin
select @intXINumTwo =XINum
from dbo.XItem
where XYNum = @intXYNumTwo
and XItem =(select XItem
from dbo.XItem
where XYNum = @intXYNumOne
and XINum = @intXINumOne)
insert into dbo.XContent(XINum,XCNO,XContent,XCLevel)
select @intXINumTwo,XCNO,XContent,XCLevel from dbo.XContent
where XINum = @intXINumOne
end
GO消息 102,级别 15,状态 1,过程 Pr_XItem_Copy,第 11 行
'=' 附近有语法错误。
WITH RECOMPILE
AS
declare @intXINumOne INT
declare @intXINumTwo INT;
delete from dbo.XItem where XYNum = @intXYNumTwo;
insert into dbo.XItem(XYNum,XINO,XItem,XIType,XIright)
select @intXYNumTwo,XINO,XItem,XIType,XIright
from dbo.XItem
where XYNum = @intXYNumOne;
while exists(select 1 from dbo.XItem where XYNum = @intXYNumOne)
begin
select @intXINumTwo =XINum
from dbo.XItem
where XYNum = @intXYNumTwo
and XItem =(select XItem
from dbo.XItem
where XYNum = @intXYNumOne
and XINum = @intXINumOne)
insert into dbo.XContent(XINum,XCNO,XContent,XCLevel)
select @intXINumTwo,XCNO,XContent,XCLevel from dbo.XContent
where XINum = @intXINumOne
end
GO