CREATE PROCEDURE [MyProcedure]
INSERT INTO My_Table(Id,Value) values (@Id,@Value)
if(@@error <> 0)
In every Insert and delete statement in sql query we should use the commit and rollback transaction. This is the one of best practices in the MSSQL query. @@error will give you if you have any error in the transaction it will give you the count. The count is 0 means no error in the transaction.