Ниже приведен пример корректного отмена транзакции при ошибке:

update Params set Value=168 where Param = 'SessionHoursLimit'

PRINT @@TRANCOUNT  
	BEGIN TRY
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
		update Params set Value=167 where Param = 'SessionHoursLimit'
		select 1/0 -- Если закомментировать эту строку то предыдущая строка выполнится
    COMMIT  TRAN 
	END TRY
	BEGIN CATCH
		if @@TRANCOUNT>0
		ROLLBACK TRAN
	END CATCH
PRINT @@TRANCOUNT  

select * from Params

Если закомментировать строку с ошибочным оператором SELECT 1/0 то предшествующая строка будет выполнена и транзакция успешно завершится, если оставить строку SELECT 1/0 то транзакция отменится полностью, и параметр Value останется таким, каким он был установлен в первой строке скрипта до начала транзакции.

Два способа выполнения транзакций в MSSQL, с отменой по ошибке.

Первый способ:

BEGIN TRY
    BEGIN TRAN
    UPDATE 1
    UPDATE 2 --Error
    UPDATE 3
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH