smnsqlexectransaction.js

let transaction = sql.transaction();

transaction.begin((err) = > {
  if(err)
    return console.log(err);
  
  sql.request(transaction)
    .input('Nome', 'Gustavo')
    .input('NomeDaMae', 'Joana')
    .execute('BuscarUsuario', (err, recordset) => {
        //Se acontecer algum erro, ROLLBACK na transaction
        if(err) {
          transaction.rollback(() => {
            console.log(err);
          });
        //Caso contrário, COMMIT
        } else {
          transaction.commit(() => {
            console.log(recordset);
          }); 
        }    
    });
})