Acabei de fazer alguns pontos aleatórios, por favor, adicione mais alguns.
1. Pré-compilado, otimizado e altamente eficiente. Isso evita a ineficiência das instruções SQL sendo transmitidas pela rede e depois interpretadas.
2. Se a empresa tiver um DBA dedicado, ele pode escrever o procedimento armazenado, e o programador só precisa chamá-lo de acordo com a interface que ele fornece. É mais claro fazer assim.
3. Fácil de modificar. É problemático modificar as instruções SQL incorporadas no programa e muitas vezes não há certeza se tudo o que deveria ser alterado foi alterado. É mais conveniente modificar o procedimento armazenado no SQL Server. Você pode alterar diretamente o procedimento armazenado, e o programa que o chama basicamente não precisa ser alterado, a menos que as alterações sejam relativamente grandes (como alterar os parâmetros passados, os dados retornados, etc.).
4. Será mais seguro. Não haverá problemas de injeção de instruções SQL.
Claro, existem desvantagens. Principalmente quando a lógica de negócios é mais complexa, seria muito cansativo escrever tudo em procedimentos armazenados.
Fonte: blog de laifangsong