La diatriba "SP" vs "Codice" è vecchia come il mondo, però qualche contributo mi sento in grado di darlo comunque:
1) L'SQL permette di gestire la concorrenza ottimistica. Perfetto, peccato che se questo ha un senso dal punto di vista tecnico è completamente errato dal punto di vista funzionale. Dire che in una entità un utente possa cambiare il campo X mentre allo stesso tempo un altro utente riesce a cambiare il campo Y potrebbe sembrare una figata, mentre invece (nella mia esperienza) nel 99% dei casi è un grave difetto. Mediamente se due campi sono nella stessa entità tra di loro c'è un legame (altrimenti che li avete messi assieme a fare?) ed è molto facile che lo scenario di cui sopra possa portare ad un'entità "logicamente corrotta" in qualche modo...
2) Le SP generano traffico inutile. Questa mi sembra veramente un dato ininfluente, si presuppone che tra application server e DB ci sia una una connessione "molto locale" (100 Mbit mi sembra il minimo, ma 1 Gbit è oramai quasi normale in ambienti di produzione "seri"), se ci dobbiamo preoccupare del traffico di rete tra applicazione e DB secondo me c'è qualcosa di profondamente sbagliato nel nostro disegno applicativo.
Altri punti a favore delle SP sono relativi al fatto che (cosa non banale) possiamo scrivere il nostro codice in maniera "semplice" facendo, in prima battuta, delle SP non ottimizzate, per poi lasciare il lavoro di ottimizzazione delle SP a chi lo sa fare (i DBA). Se teniamo il codice dentro le nostre DLL l'iterazione "ottimizza l'SQL-Compila-Deploya-Testa" è infinitamente più costosa (credetemi sulla fiducia, ma c'è un ordine di grandezza di efficienza).
Riguardo al codice SQL ricordiamoci poi che il costo di gestione delle query da parte dell'ottimizzatore (il parsing) usando query abbastanza dinamiche è assolutamente sensibile (mentre per le SP questo normalmente non è misurabile) e che, a meno di non tenerlo presente quando si scrive del codice, una SP avrà quasi certamente un query plan memorizzato in cache o comunque pre-compilato (a meno che non si usi codice dinamico dentro la SP, cosa abbastanza bruttina) mentre la query SQL non è detto, le condizioni che portano il DB server a non rifarsi un query plan ad hoc per ogni query sono abbastanza limitative (soprattutto su Oracle, che è abbastanza più schizzinoso di SQL Server riguardo a cosa tenere in cache e cosa no).
Insomma, al 90% io sono a favore delle SP. Devo dire che ho cambiato radicalmente parere da circa 2 anni e mezzo, in corrispondenza del cambio di lavoro. Prima ne facevo uno in cui le performance erano relativamente poco importanti (nel senso che le mie applicazioni avevano mediamente pochi utenti), poi sono passato a fare applicazioni mission critical con parecchi client collegati ed in cui le performance sono assolutamente fondamentali ed ho cambiato parere, le SP "sono bene", soprattutto per le performance.