inserts and updates issue using sp

Hi, i have the following sp. im using vs.net2005, sqlserver2005.

now how do i implement functions like - > okay using datasets, exception is thrown that the row has been modified and i get to inform the user, reload the modified row, .

how do i get same functionality using sp? is that possible ? how/

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERProcedure [dbo].[sp_Insert_Or_Update_Bill]

@.BillID_All uniqueidentifier,

@.BillID uniqueidentifier,

@.Pono nvarchar(25),

@.Date_ smalldatetime,

@.SupplierCode nvarchar(25),

@.Reference nvarchar(25),

@.AmountType nchar(10),

@.BillType nvarchar(25),

@.TypeCode nchar(10),

@.AmountFC decimal(18,2),

@.ROE decimal(9,2),

@.Currency nchar(5),

@.LinkBill uniqueidentifier,

@.multiplicityID uniqueidentifier,

@.payment_or_bill smallint

as

IfExists(Select*from bill where billid_all= @.BillID_All and amounttype = @.amounttype)

begin

update bill

SET [BillID_All] = @.BillID_All

,[BillID] = @.BillID

,[Pono] = @.Pono

,[Date_] = @.Date_

,[SupplierCode] = @.SupplierCode

,[Reference] = @.Reference

,[AmountType] = @.AmountType

,[BillType] = @.BillType

,[TypeCode] = @.TypeCode

,[AmountFC] = @.AmountFC

,[ROE] = @.ROE

,[Currency] = @.Currency

,[LinkBill] = @.LinkBill

,[multiplicityID] = @.multiplicityID

,[payment_or_bill] = @.payment_or_bill

where billid_all= @.BillID_All and amounttype = @.amounttype

end

else

ifnot @.AmountFC = 0

begin

begin

INSERTINTO [Costing].[dbo].[Bill]

([BillID_All]

,[BillID]

,[Pono]

,[Date_]

,[SupplierCode]

,[Reference]

,[AmountType]

,[BillType]

,[TypeCode]

,[AmountFC]

,[ROE]

,[Currency]

,[LinkBill]

,[multiplicityID]

,[payment_or_bill])

VALUES

(@.BillID_All

,@.BillID

,@.Pono

,@.Date_

,@.SupplierCode

,@.Reference

,@.AmountType

,@.BillType

,@.TypeCode

,@.AmountFC

,@.ROE

,@.Currency

,@.LinkBill

,@.multiplicityID

,@.payment_or_bill)

end

end

This is known as optimistic locking, and it requires you to check to see if the copy of the data you originally fetched still exists using one of a few methods:

1. Pass all of the non-modified data you have in the table to the procedure and check to see if it has changed. That is probably how your app does it now. Not very efficient, and won't work with text.

2. Add a rowversion (commonly called timestamp) column to the table. Then check to see if it has changed. rowversions manage themselves, so every modification to the table will spawn a change to the rowversion.

ALTER TABLE test add row_version rowversion

Then in your update or delete:

...
,[payment_or_bill] = @.payment_or_bill
where billid_all = @.BillID_All
and amounttype = @.amounttype
and row_version = @.row_version --value fetched with to the client.

if @.@.rowcount <> 1 --what you expected
begin
if not exists (select --check to see if a row exists with the primary key value you are trying to update
raiserror ('The row has been modified',16,1)

3. Add an update_date to tables and check this. The downside here is that you have to manage the change values for yourself with a trigger, or even in the stored procedures (I always use triggers). Do the same sort of check as for the rowversion

It takes some work, but the last two are far more efficient than number 1...