Problem returning a timestamp column inside an TSQL Transaction

I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@.LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @.LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@.NewLastChanged"):

1select @.TimestampCheck = LastChangedfrom ADD_Addresswhere AddressId=@.AddressId23if @.TimestampCheckisnull4begin5RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1)-- AddressId does not exist.6return -17end8 else if @.TimestampCheck <> @.LastChanged9begin10RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)11return -112end131415Begin Tran Address1617Update ADD_Address18set StreetNumber= @.StreetNumber, AddressLine1=@.AddressLine1, StreetTypeId=@.StreetTypeId, AddressLine2=@.AddressLine2, AddressLine3=@.AddressLine3, CityId=@.CityId, StateProvidenceId=@.StateProvidenceId, ZipCode=@.ZipCode, CreateId=@.CreateId, CreateDate=@.CreateDate19where AddressId= @.AddressId2021select @.error_code =@.@.ERROR, @.AddressId= scope_identity()2223if @.error_code = 024begin25commit tran Address2627select @.LastChanged = LastChanged28from ADD_Address29where AddressId = @.AddressId3031if @.LastChangedisnull32begin33RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)34return -135end36if @.LastChanged = @.TimestampCheck37begin38RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)39return -140end41return 0

I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?

Thank you,

jspurlin 

No need for a transaction for that really. Just go about it a different way:

Update {fields} FROM {table} WHEREAddressID=@.AddressID ANDLastChanged=@.LastChanged

Then get the number of records affected, and the error codes.

if there were no errors, and no rows were affected then you can either raise a generic error ('record changed or does not exist'), or you can go and look and see which of the two (AddressID,LastChanged) didn't exist, although there is the possability the data may change between the update and when you go and try to figure out why it failed, in which case you may get an incorrect error message (Saying it didn't exist, when it was only changed or saying it was changed when it didn't exist).