My colleague Dhiraj Ranka wrote about a very interesting topic of SQL Injections.
Though Stored Procedures provide certain protection from SQL injections, an improper implementation voids all such protections.
Dhiraj has demonstrated an SQL injection in a Stored Procedure which has not been constructed properly.
The crux of the issue lies in using the system Stored Procedure sp_executesql which takes a string as parameter and executes it. The string is generally a SQL query. So the entire premise of using stored procedures to prevent query injections fails as the input is directly inserted into the SQL query.
Read the detailed example at http://dhirajranka.wordpress.com/2009/08/25/sql-injection-stored-procedure/
Another interesting account of improper usage of Stored Procedure is demonstrated at