A stored procedure is a named group of SQL statements that 
have been previously created and stored in the server 
database. Stored procedures accept input parameters so that 
a single procedure can be used over the network by several 
clients using different input data. And when the procedure 
is modified, all clients automatically get the new version. 
purpose:
Stored procedures reduce network traffic and improve 
performance. Stored procedures can be used to help ensure 
the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

Stored procedure is nothing but a Set of T-Sql statements 
combined to perform a single task of several tasks. it's 
like a macro

There are two type of stored procedures. System Stored 
Procedure and User Stored Procedure (User Defined Stored 
Procedure).

User SProcs serves the same purpose as the system SProcs. 
Now the question comes what is the need to write a stored 
procedure? 
Suppose in your organization there are certain queries 
which are used very frequently or there are certain queries 
in which you don't want users to know that what all tables/ 
views etc. are scanned to get the desired result or, for 
faster execution of a frequently used query you write 
stored procedures.