Why to use StoredProcedures ?
Stored Procedures can be reused from different webpages, classes or webapplication where in case of coding sql statements you have to write code in each and every place.
Stored Procedures uses parameters to receive user input values so helps to prevent SQL injection.
Stored procedure are in complied form i.e they are already in compiled mode you nedd need to call them in your application. wherein when you write sql query the processor has to parse it, analyze it and execute it. so the stored procedure are faster than coding sql queries.
Stored Procedures are easy to debug.
Also from security point of view we can provide credentials to execute the stored procedure so this means that users access to the database can be defined by only allowing them permissions to execute the allowed stored procedures while denying them permissions to the underlying database objects such as tables and views that are used by the stored procedures.
Sunil Yadav
Is there is a possibility of SQL Injection in Stored Procedure?
Mainly SQL Injection attacks are found in dynamic sql queries, for instance if you are using sp_executesql to execute a Transact-SQL statement or batch. MS also says that use of sp_executesql can lead to malicious attack like sql injection.
http://msdn.microsoft.com/en-us/library/ms188001.aspx