October 16, 2010 at 7:34 am
in sql server 2005 in SP.... i have a try- catch block.... in try block based on condition can i diverted to coltrol to catch block?
begin try
begin transaction
insert stmt
if value>1
--it should rollock
commit
end try
begin catch
if @@transcount>0
rollback
end catch
or can i use rollback in try block itself?
September 20, 2013 at 6:37 am
Within the CATCH block, you can commit you can commit or roll the current transaction unless the transaction cannot be committed and must be rolled back. To test for the state of the transaction, you can query the EXACT_STATE function.
[font="Comic Sans MS"]System going down at 5 this afternoon to install scheduler bug[/font]. 😀
September 20, 2013 at 8:20 am
You can have a rollback inside the try block.
begin try
begin transaction
insert stmt
if value>1
rollback --Or you could use RAISERROR
--Raising an error might be a better approach if you want to log that error or do something with it
else
commit
end try
begin catch
if @@transcount>0
rollback
end catch
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply