update command in a transaction not change the record

  • Dear all,

    I have a transaction to call below stored procedure. I found sometimes the command after update command in bold below is executed but the update command in bold is executed but not take effect at the end. I have put a trigger to the table and it did confirmed that the update command runs.

    CREATE PROCEDURE npitool_upd_domsitems

    @reqid int,

    @partnumber varchar(10),

    @classcode varchar(10),

    @subclass varchar(10),

    @updated char(1)

    AS

    begin

    update domsupdate set updated = @updated where reqid = @reqid and partnumber = @partnumber

    if( @updated = 'Y' )

    begin

    declare @doms_add table(mod varchar(10),siteid tinyint)

    insert into @doms_add(mod, siteid)

    select @partnumber, siteid from site a, domsupdateinfo b where b.reqid = @reqid and a.site like b.mfg + '%' and b.mfg <> ''

    delete a from @doms_add a, doms_updated b where a.mod = b.mod and a.siteid = b.siteid

    update modlist set classcode = @classcode from modlist a, part b where a.partid = b.partid and b.partnumber = @partnumber and b.description like 'BASE,%' and a.classcode <> @classcode

    insert into doms_updated(mod, siteid, instime, reqid)

    select mod, siteid, getdate(), @reqid from @doms_add

    end

    end

    GO

    Can you please help to expain it? Thanks in advance.

    coby

  • Hi

    Is the value being reverted back inside the procedure or from some other procedure.

    "Keep Trying"

  • I think you can use transaction like in following example

    Begin try

    Begin Transaction

    ----- your code

    Commit Transaction

    End Try

    Begin Catch

    Roll back Transaction

    End Catch

    Try statement automatically handles your error for not commited record. if you found any error then post it will roll back whole process.

    remember if you want to call another sp in this then this will not work for that in this way

  • Is your db case sensitive. In that case you can check if your are passing the value 'y' in upper case or lower case.

    "Keep Trying"

  • Hi Coby,

    I agree with what chirag says, may be u need to check whether the case sensitivity is the reason for ur misinterpretation (if any).

    If its not so and condition is perfectly fine, then the only reason behind ur update statement not working is not getting the output based on the given criteria..

    I would suggest to check the records with the same criteria in SELECT statement..

    eg: Select * from tablename where Criteria...

    If it doesnt gives you result then not to worry about your update statement...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply