Ease of maintenance
Stored procedures are easier to maintain than T-SQL scripts imbedded in client applications. The DBA has full access to them and can change them (for example insert a RETURN as the first row as temporary measure) in case of emergency without recompiling/reloading the application programs. In normal situation though the fix should go through development and QA.
Ease of testing
Properly defined stored procedures, with well-documented and consistent outputs, are quite easy to test independently from the application layer. They can be tested from the query editor window or files containing test run scripts using command line utilities such as SQLCMD. Imbedded SQL scripts are very difficult to test and QA.
Ad hoc SQL scripts in applications may be more vulnerable to being hacked, and SQL Server 2005 may be unable to secure access to data. Stored procedures can help to battle such problems as SQL injection or escalation attacks, in which an attacker gains rights to access resources other than those, which he/she is authorized for.
Stored procedures generally provide better performance than client applications with T-SQL scripts due to reduced network usage. This is especially true for multi-step client application scripts whereby data has to be passed back and forth between the server and client. For frequently used stored procedures, the compiled execution plans reside in procedure cache(memory), further helping with performance.
Overview of SQL Server Stored Procedure