sql server - How to prevent an error in Try..Catch from blocking the transaction? -


i have tsql code following pattern:

begin tran;  declare @set int; begin try     set @set = dbo.udfthatcantriggeranerror('bycastingthedescriptiveerrortoint'); end try begin catch     set @set = -1;  --default value; end catch  --really in sproc called here shown here brevity save tran testing;  rollback tran testing;  --back original sproc rollback tran; 

(in reality rollbacks triggered if there further errors inside sprocs, gives idea.)

in testing on sql server 2008 r2 sp1, transaction save operation consistently throws error: the current transaction cannot committed , cannot support operations write log file. roll transaction.

if change udf call 1 doesn't raise error, or replace line different way of setting variable, code completes.

it'd so easier if try..catch blocks here made work in same way analogous blocks in .net , let me elect reset error flag, rather automatically assuming every last error terminal - 1 isn't, , less code alternative way of controlling flow.

so - there way reset error status inside catch block or need rewrite logic avoid caught error being thrown in first place?


Comments

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

caching - How to check if a url path exists in the service worker cache -

Redirect to a HTTPS version using .htaccess -