July 27, 2009 at 11:44 pm
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
July 28, 2009 at 4:04 am
Hi
Is the value being reverted back inside the procedure or from some other procedure.
"Keep Trying"
July 28, 2009 at 4:50 am
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
July 29, 2009 at 1:45 am
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"
July 29, 2009 at 4:19 am
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