SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE upSetPDataHead
@LANCfg INT,@Chid INT,@D INT
AS--DECLARE @Chid INT
DECLARE @MChid INT
IF EXISTS(SELECT LANCfg FROM TblPDataHead WHERE LANCfg=@LANCfg)
BEGIN
--SELECT @Chid=Chid FROM TblPDataHead WHERE LANCfg=@LANCfg
SELECT @MChid= max(Chid) FROM TblPDataHead WHERE LANCfg=@LANCfg
IF @D<0 AND @Chid>1
/*工步减1*/
BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid-1 AND LANCfg=@LANCfg
UPDATE TblPDataHead SET Chid=@Chid-1 WHERE Chid=@Chid AND LANCfg=@LANCfg--RETURN
END
IF @D>0 AND @Chid<@MChid
/*工序加1*/
BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
COMMIT TRAN
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
COMMIT TRAN
--RETURN
END
--RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO--RETURN
END
IF @D>0 AND @Chid<@MChid
/*工序加1*/
BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
COMMIT
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
COMMIT
--RETURN
END
--RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO相连两个update语句,为什么只有后面update语句起作用
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE upSetPDataHead
@LANCfg INT,@Chid INT,@D INT
AS--DECLARE @Chid INT
DECLARE @MChid INT
IF EXISTS(SELECT LANCfg FROM TblPDataHead WHERE LANCfg=@LANCfg)
BEGIN
--SELECT @Chid=Chid FROM TblPDataHead WHERE LANCfg=@LANCfg
SELECT @MChid= max(Chid) FROM TblPDataHead WHERE LANCfg=@LANCfg
IF @D<0 AND @Chid>1
/*工步减1*/
BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid-1 AND LANCfg=@LANCfg
UPDATE TblPDataHead SET Chid=@Chid-1 WHERE Chid=@Chid AND LANCfg=@LANCfg--RETURN
END
IF @D>0 AND @Chid<@MChid
/*工序加1*/
BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
COMMIT TRAN
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
COMMIT TRAN
--RETURN
END
--RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO--RETURN
END
IF @D>0 AND @Chid<@MChid
/*工序加1*/
BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
COMMIT
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
COMMIT
--RETURN
END
--RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO相连两个update语句,为什么只有后面update语句起作用
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
go
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
否则你有一部分数据发生两次更新,恢复原样了吧
1 1 1
2 2 1==
set @child = 1
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
COMMIT ---id child lancfg
1 1 1
2 1 1UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
COMMIT id child lancfg
1 2 1
2 2 1
----
交换chid的值???
insert @t select 'd',1
insert @t select 'c',2
insert @t select 'b',3
begin
declare @i intset @i = 1
update @t set orderflag = @i where orderflag = @i + 1
update @t set orderflag = @i+1 where orderflag = @i
endselect * from @t
/*name orderflag
---- -----------
d 2
c 2
b 3(所影响的行数为 3 行)*/
delete from @tinsert @t select 'd',1
insert @t select 'c',2
insert @t select 'b',3
begin
update @t
set orderflag = case when orderflag = @i then @i + 1 when orderflag = @i+1 then @i else orderflag end
endselect * from @t/*
name orderflag
---- -----------
d 2
c 1
b 3(所影响的行数为 3 行)*/
insert into ttt1 select 5,'aa'
insert into ttt1 select 6,'bb'declare @chid int
set @chid=5
update ttt1 set chid=case when a.chid=@chid then @chid+1 when a.chid=@chid+1 then @chid end
from ttt1 a select * from ttt1chid name
6 aa
5 bb
SET case when Chid=@Chid then Chid=@Chid + 1
when Chid=@Chid+1 then @Chid
else Chid end
WHERE LANCfg=@LANCfg and chid between @chid and @chid + 1
update 语句本身就是隐含事务吧。
create table ttt1 (chid int,name varchar(10))
insert into ttt1 select 5,'aa'
insert into ttt1 select 6,'bb'declare @chid int
set @chid=5
update ttt1 set chid=case when a.chid=@chid then @chid+1 when a.chid=@chid+1 then @chid end
from ttt1 a select * from ttt1chid name
6 aa
5 bb
新建的数据表中可以用,在我的数据表中就有问题了提示chid不可以插入null消息515级别16状态2
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE upSetPDataHead
@LANCfg INT,@Chid INT,@D INT
AS
DECLARE @Id INT
DECLARE @MChid INTIF EXISTS(SELECT LANCfg FROM TblPDataHead WHERE LANCfg=@LANCfg)
BEGIN
SELECT @Id= DHid FROM TblPDataHead WHERE LANCfg=@LANCfg and Chid=@Chid
SELECT @MChid= max(Chid) FROM TblPDataHead WHERE LANCfg=@LANCfg
IF @D<0 AND @Chid>1BEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid-1 AND LANCfg=@LANCfg
UPDATE TblPDataHead SET Chid=@Chid-1 WHERE DHid= @Id
Return
END
IF @D>0 AND @Chid<@MChidBEGIN
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE DHid= @Id
Return
END
Return
ENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
谢谢各位的回答,问题已解决。现把答案添出来