How can transaction be maintained in a procedure?

It helps in ensuring that either all the SQL statements written in between the transaction run successfully or no statement should be executed successfully. I.e. If there are 10 statements in the procedure and it fails on the 9 statements then all the statements should be rolled back. This also ensure that any other query which is executing can make an impact in between as it will commit till it has executed all the statements

create proc usp_transaction_handle

  As

  Begin try

  Begin transaction

insert into input_values

  select 'One','Two'

RAISERROR ('User define error', 16, 1)

update input_values set col2='two'

where col1='One'

  If (@@TRANCOUNT>0)

Commit transaction

 End try

BEGIN CATCH

 ROLLBACK

         Print 'Rollback done'

END CATCH

select * from input_values

go

exec usp_transaction_handle

go

select * from input_values

Sample for successful transaction:

alter proc usp_transaction_handle_co

  As

  begin

  Begin try

  Begin transaction

insert into input_values

  select 'One','Two'

update input_values set col2='two'

where col1='One'

     If (@@TRANCOUNT <> 0)

    begin

    Commit transaction

      print 'Commit done'

      end

End try

BEGIN CATCH

 ROLLBACK transaction

     print 'ROLLBACK done'

END CATCH

end

select * from input_values

go

exec usp_transaction_handle_co

go

select * from input_values