sql server - TSQL Try / Catch within Transaction or vice versa? -

i'm writing script delete records number of tables, before deletes must return count user confirm before committing.

this summary of script.

begin transaction scheduledelete     begin try         delete   -- delete commands full sql cut out         delete   -- delete commands full sql cut out         delete   -- delete commands full sql cut out         print 'x rows deleted. please commit or rollback.' --calculation cut out.     end try     begin catch          select             error_number() errornumber,             error_severity() errorseverity,             error_state() errorstate,             error_procedure() errorprocedure,             error_line() errorline,             error_message() errormessage              rollback transaction scheduledelete             print 'error detected, changes reversed.'     end catch  --commit transaction scheduledelete --run if count correct.  --rollback transaction scheduledelete --run if there doubt whatsoever. 

this first time writing transaction, correct/best practice have try/catch block inside transaction or should transaction inside try block?

the important factor in script user must manually commit transaction.

only open transaction once inside try block , before actual statement, , commit straightaway. not wait control go end of batch commit transactions.

if goes wrong while in try block , have opened transaction, control jump catch block. rollback transaction there , other error handling required.

i have added little check open transaction using @@trancount function before rolling transaction. doesn't make sense in scenario. more useful when doing validations checks in try block before open transaction checking param values , other stuff , raising error in try block if of validation checks fail. in case, control jump catch block without opening transaction. there can check open transaction , rollback if there open ones. in case, don't need check open transaction not enter catch block unless goes wrong inside transaction.

do not ask after have executed delete operation whether needs committed or rolled back; these validation before opening transaction. once transaction opened, commit straightaway , in case of errors, error handling (you doing job getting detailed info using of error functions).

begin try    begin transaction scheduledelete     delete   -- delete commands full sql cut out     delete   -- delete commands full sql cut out     delete   -- delete commands full sql cut out  commit transaction scheduledelete     print 'x rows deleted. operation successful tara.' --calculation cut out. end try  begin catch    if (@@trancount > 0)    begin       rollback transaction scheduledelete       print 'error detected, changes reversed'    end      select         error_number() errornumber,         error_severity() errorseverity,         error_state() errorstate,         error_procedure() errorprocedure,         error_line() errorline,         error_message() errormessage end catch