Stored Procedure (SP, here after I will refer it as SP) is as its name indicates stored in a database . SP contains one or more then single T-SQL in it, and it is re-usable. The beauty or advantage of SP is, it will get complied and stored in Database, now when ever you required to run the T-SQL or bunch of T-SQL you don't need to write it and compile it again, just call that Stored Procedure(SP).
Whereas in case of individual T-SQL it will get complied every time you run them; so it helps to reduce your time writing T-SQL, it saves CPU and I/O times it would take to compile and to create execution plan.
There are some advantages of using SPs, they are:
1. You do not need to rewrite your T-SQL which you would required to run often
2. It will get complied and stored in DB, and this will helps you saving your CPU & I/O cycle. * In case if some modification is done in SP you would required to re-compile it.
3. Can accept parameter as input value and will return output based on it
4. You can call one SP in another
5. You can have security configured for them, i.e. can grant or deny exeecute permission for particular user/role/group
6. SP which has parameters can help you protect from SQL Injection Attack
7. SP will help you reduce your network round-trip and hence network traffic
Here are some basic instruction and input on how to write SP(s).
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx http://www.sql-server-performance.com/articles/dev/create_stored_procedures_tsql_p1.aspx
