Use of Stored procedure

The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

They allow modular programming.

You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.

They allow faster execution.

If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

They can reduce network traffic.

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

They can be used as a security mechanism. Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

Source: http://msdn.microsoft.com/en-us/library/aa214299%28SQL.80%29.aspx