try-catch block - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

try-catch block

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-07-14 : 01:02:34

Can i anyone correct my below query:

incase Process -3 gets failed the entire things get rollbacked,i need till process-2 to get commited and only process 3 should be get rollbacked and entry should be there in my status_report table.

Create Procedure RBI_CONTROL_SP
AS
begin
set nocount on

begin try
BEGIN TRANSACTION

insert into fin_wh..Status_report
(
[object_name],
row,
st_date
)
select
'Data Tranfer',
'Inprogress--',
getdate()

--process (1)
--Truncating the source database[1 minute]
exec fin_ods..trun_sp

--Process(2)
--Data Transfer From one server to another server [Approximately 4 to 5 minutes]
exec fin_ods..RBI_Data_Transfer_sp

--Process (3)--temp table Table population,Fetching data from the one database to another database in same server][Approximately 7 minutes]
exec FIN_wh..RBI_SPExecution_sp

Update fin_wh..Status_report
set row = 'Success',
[error_message] = 'No Error',
end_date = getdate()
where row = 'Inprogress--'

COMMIT TRANSACTION
end try

begin catch
rollback transaction

Update fin_wh..Status_report
set row = 'Failure',
end_date = getdate(),
[error_message]= cast(error_number() as varchar) + '***' + error_message()
where row = 'Inprogress--'

end catch

set nocount off
end

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-14 : 03:25:02

Pardon me if I am wrong, but transaction will be rollback as a group or committed as a group right?

So if process 1 and 2 is to be committed before running process 3, then it does not make sense to store them in the same transaction. You could execute process 1 and 2 not as part of the transaction before running process 3 (i.e. Process 3 is stand alone transaction).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources