Stored procedures greatly enhance the power, efficiency, and flexibility of SQL Server, and dramatically improve the performance of SQL statements and batches.
Stored procedures can:
Stored procedures on other SQL Servers to which the client process is not directly connected to can be executed if the remote server has been set up to allow remote logins. You can write triggers on your local SQL Server that execute procedures on a remote server whenever certain events (inserts, updates, or deletes) occur locally. (For details about triggers, see Using Triggers to Define Business Processes.)
Stored procedures differ from ordinary SQL statements and from batches of SQL statements in that they are pre-parsed and pre-normalized. The first time you run a procedure, the SQL Server query processor analyzes it and prepares internal, normalized structure for the procedure that is stored in a system table. The first time the procedure is run when the SQL Server is started, the procedure is then brought into memory and fully compiled (but it does not need to be re-parsed or "sequenced" since this was done when the procedure was created). The fully compiled plan then stays in memory (unless other memory needs force it out so that the next request for execution (whether by the same or a different client) can be processed without any plan compilation overhead.
Stored procedures can serve as security mechanisms, since a user can be granted permission to execute a stored procedure even if he or she does not have permissions on tables or views referenced in it. (For details, see the Microsoft SQL Server Administrator's Companion.)