create Table AA
(UYEAR varchar(4),UDIV varchar(1),AUID int)
2008 L 2
2008 A 3
如果输入 2009 L
想要的结果是 2009 L 1
如果输入 2008 L
想要得结果是 2008 L 3
proc
ALTER PROCEDURE dbo.StoredProcedure2
@UYEAR as varchar(4),
@UDIV as varchar(1),
@ERRORCODE as int output,
@AUID as int output
AS
/* SET NOCOUNT ON */
Begin Transaction
SELECT @AUID=AUID FROM AA
WHERE UYEAR = @UYEAR AND UDIV = @UDIV
if @@ROWCOUNT < = 0
begin
print 'AA'
INSERT INTO AA(UYEAR,UDIV,AUID)
VALUES (@UYEAR,@UDIV,1)
print @@error
end
else
begin
UPDATE AA
SET UYEAR = @UYEAR,
UDIV = @DIV,
SYOZOKU_NM = SYOZOKU_NM+1,
WHERE UYEAR = @UYEAR and UDIV= @UDIV
print 'BB'
print @@error
end
IF @@ERROR <> 0
BEGIN
Rollback Transaction
SET @ERRORCODE = 3
RETURN
END
Commit Transaction
SET @ERRORCODE = 0
RETURN
为啥执行的结果不正确呢？
求救
------解决方案--------------------
搞这么复杂做什么
------解决方案--------------------
- SQL code
ALTER PROCEDURE dbo.StoredProcedure2 @UYEAR as varchar(4), @UDIV as varchar(1), Asif exists(Select 1 FROM AA WHERE UYEAR = @UYEAR AND UDIV = @UDIV ) Select UYEAR,UDIV,AUID+1 FROM AA WHERE UYEAR = @UYEAR AND UDIV = @UDIVelse Select [email protected],'UDIV' [email protected],'AUID'=1
------解决方案--------------------
- SQL code
create Table AA (UYEAR varchar(4),UDIV varchar(1),AUID int) ------------------------------搞那么复杂？create proc wsp@uyear varchar(4),@udiv varchar(1)as if exits(select 1 from AA where [email protected] and [email protected]) update aa set auid=auid+1 where [email protected] and [email protected] else insert into aa select @uyear,@udiv,1
------解决方案--------------------
好的，呵呵
不过还有一个问题。
因为我这个自动加1，是要实现派他操作的。
如果没有 Transaction的话，会不会出现同一时间更新操作呢？
我的AUID就是想实现银行账户的流水号，不管谁操作，都不能重复。
--------------------
if exits(select 1 from AA where [email protected] and [email protected])
update aa set auid=auid+1 where [email protected] and [email protected]
else
insert into aa select @uyear,@udiv,1
一次只有一个DML语句在执行,一个DML语句默认就是一个Transaction
所以不会的