hi friend,i have an sp it does insert/update operations on a table. what i should do, if insert / update is sucessful it shold return 1, if the record exist return 2 and fialed to insert / update return 3The problem is if you think interms of a UI, user fills a form clicks save its gets inserted and 1 was retured. When the user click s the save buttons again (consecutively) it should return error code 2, with the message "record already exist woudl you liek to override or view". My SP works that far but if you go through code, the sp figures out weather its a fresh record or existing record ussing the value in @pidx if this is null or 0 its fresh else existing.when the 1 was returned to the UI and the user clicks save again teh control comes to the else block and returns 2, and error messages record already exist. but when the user selects to override it cannot execute any statement in the else block other than record alredy exist block, there fore the update never happense, how do i fix thsi? i need it to reurn 1 in success insertion or update and 2 when the record exists and 3 when failed.create proc mst_sp_test5 @pcompanyname nvarchar(30), @pcode char(6), @pidx int = 0 OUTPUT, @presult smallint = 0 OUTPUT, @perrmsg nvarchar(max) = null OUTPUT, @prversion timestamp = null OUTPUTasbegin -- if fresh record if @pidx = 0 or @pidx si null begin -- genereate code and insert recod end else -- update -- i have to return the "message" and "presult"... record already exists, 2. -- check for deletion if exists (select 1 from tblTest2 where idx = @pidx) begin set @pidx = @pidx; set @presult = 2; select @prversion = RVersion from tblTest2 where idx = @pidx; raiserror('record already exists', 16, 1) end -- check for deletion if exists (select 1 from tblTest2 where idx = @pidx) begin set @pidx = 0; set @presult = 3; set @prversion = null; raiserror('record has been deleted', 16, 1) end -- check for concurrencies else if not exists (select 1 from tblTest2 where idx = @pidx and RVersion = @prversion) begin set @pidx = @pidx set @presult = 3; select @prversion = RVersion from tblTest2 where idx = @pidx; raiserror('Optimistic concurrecy violation occured', 16, 1) end else begin -- update statements update tblTest2 set code = @pcode, companyname = @pcompanyname, updateddate = getdate() where code = @pcode if @@rowcount > 0 begin set @pidx = scope_identity(); set @presult = 1; set @perrmsg = null; select @prversion = RVersion from tblTest2 where code = @pcode; select @pidx, @presult, @perrmsg, @prversion end else begin set @pidx = 0; set @presult = 3; select @prversion = RVersion from tblTest2 where code = @pcode; raiserror('update operation failed', 16, 1) end end end end try begin catch set @perrmsg = error_message(); select @pidx, @presult, @prversion, @perrmsg end catchendcheers
My SP returns incorrect errors and codes?
Posted - 2010-06-10 : 05:18:29