The stored procedure is another way of executing a query against a database/databases on the sql server, the only difference is that query is stored on the actual server and to execute it you will need to call it.
this feature is usefull for repetitive tasks and if you don’t want the query to be stored on the client machine (example would be a helpdesk first line support person needs to query the database many times a day for a status on the logged calls)
The stored procedure is useful when you want to lock the query down only to some users.
I have described you a very small area where you can apply the stored procedures
I presume you have read this:
<a href=”http://msdn.microsoft.com/en-us/library/ms191436.aspx”>Stored Procedure Basics</a>