我有一个实现乐观锁定的更新存储过程。存储过程如下所示:
ALTER PROCEDURE [dbo].[usp_Test]
@Id AS char(2),
@recordTimestamp as timestamp
...
BEGIN
UPDATE XY
..
WHERE ((Id = @Id) AND (recordTimeStamp = @recordTimestamp))
if @@rowcount = 0
begin
RAISERROR ('this row was changed by another user', 18, 1)
end
SELECT timeStamp from XY where Id = @Idend
有没有更简单的方法来返回新时间戳?我真的很想避免SELECT
陈述。
假设至少可以使用 SQL Server 2005OUTPUT
UPDATE XY
SET Col = 'foo'
OUTPUT inserted.recordTimeStamp
WHERE ((Id = @Id) AND (recordTimeStamp = @recordTimestamp))
或者使用表变量来更接近地反映原始查询的行为的版本。
DECLARE @Timestamp TABLE(stamp binary(8))
UPDATE XY
SET col='foo'
OUTPUT inserted.recordTimeStamp INTO @Timestamp
WHERE (Id = @Id) AND (recordTimeStamp = @recordTimestamp)
if @@rowcount = 0
begin
RAISERROR ('this row was changed by another user', 18, 1)
end
SELECT stamp
FROM @Timestamp
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)