detecting Update Conflict

When a user try to update the record I use timestamp field (upsize_ts) to
figure out if data was modified by other user.
However I found out @.@.DBTS may cause problem in Multi user senario and may
not return correct Tiemstamp value as it is not the timestamp for the curren
t
operation but for the database is there any inexpensive solutin to resolve
this.
Thanks
Tanweer
CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
@.rowversion timestamp OUTPUT,
@.people_phone_id int,
@.offsysid varchar(5),
@.people_id int=Null,
@.sequence_id smallint=Null,
@.code_type varchar(5)=Null,
@.code_value varchar(50)=Null,
@.modify_operator varchar(15)=Null,
@.upsize_ts timestamp=Null
AS
SET NOCOUNT OFF
DECLARE @.errormsg varchar(1000)
DECLARE @.errcode int
DECLARE @.rowcount int
DECLARE @.modify_date datetime
SET @.modify_date = GetDate()
UPDATE [dbo].[PEOPLE_PHONE] SET
[people_id] = case when @.people_id is null then people_id else @.people_id
end,
[sequence_id] = case when @.sequence_id is null then sequence_id else
@.sequence_id end,
[code_type] = case when @.code_type is null then code_type else @.code_type
end,
[code_value] = case when @.code_value is null then code_value else
@.code_value end,
[modify_date] = case when @.modify_date is null then modify_date else
@.modify_date end,
[modify_operator] = case when @.modify_operator is null then modify_operator
else @.modify_operator end
WHERE
[people_phone_id] = @.people_phone_id
AND [offsysid] = @.offsysid
AND [upsize_ts] = @.rowversion
SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
--Use it to check the value of Timestamp column
--print 'Timestamp' + convert(varchar(50),@.rowversion)
select @.rowversion
IF(@.errcode = 0)
BEGIN
IF(@.RowCount = 0)
BEGIN
IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
@.people_phone_id AND [offsysid] = @.offsysid)
BEGIN
RAISERROR('Row not updated because it was changed', 15, 1)
RETURN 50003
END
ELSE
BEGIN
RAISERROR('Row is gone', 15, 1)
RETURN 50004
END
END
ELSE
PRINT 'Row updated'
END
ELSE
PRINT 'Error'
RETURN @.errcode
GOeasist of all..
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
ALL OPERATIONS
COMMIT TRAN
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||A doubt, why do you bother in this scnario. There is only one update and no
select and no transaction. You will never get an erronous update in this cas
e.
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||Don't use @.@.dbts, but rather retrieve the actual value for the updated row.
If more than one row is updated at a time, then you'd have to store the
values in a temporary table (e.g. a table variable) or use the OUTPUT clause
in SQL 2005.
ML
http://milambda.blogspot.com/|||I guess I have to go with ML syggestion there is no easy way out.
May be a wish for SQL 2005 Service Pack2
Thanks
Tanweer
"ML" wrote:

> Don't use @.@.dbts, but rather retrieve the actual value for the updated row
.
> If more than one row is updated at a time, then you'd have to store the
> values in a temporary table (e.g. a table variable) or use the OUTPUT clau
se
> in SQL 2005.
>
> ML
> --
> http://milambda.blogspot.com/|||are you using sql server 2005'
--
"Tanweer" wrote:
> I guess I have to go with ML syggestion there is no easy way out.
> May be a wish for SQL 2005 Service Pack2
> Thanks
> Tanweer
> "ML" wrote:
>