DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to benefit from sprocs versus client applications with scripts?

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.

Greater security
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.

Better performance
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.

Related article:

Overview of SQL Server Stored Procedure

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE