Enough bullshitting, lets start from the the following scenario, we have an application that need a database (a rare event, you can imagine).
We have three tiers of users here, normal users can view data, editors can change data and the administrator can do whatever they want. It's a simple model, but it is enough for this example. Here are several security treats for the application, depending on the way it was implemented.
A hacker gained access to the database and is able to send any query that it wants to the server.
If we were using the Stored Procedures (SP)apporach, he doesn't have access to the tables, and can only call stored procedures, whose names are not known to the hacker.
If we were using the Parameterized Queries (PQ) approach, he has access to tables, based on the user's role, but he doesn't know the names of the tables.
Next step for the hacker is to start guessing. In both approach, he is likely to try to use the rich meta-data capabilities of the server to find out what the database structure is like. Because the database is well-admined, the attempt will fail (for both cases), since the application user will not have access to such tables as sys.objects, etc.
After realizing that the easy path is out, the hacker starts guessing stuff like:
(SP): sp_AddEmployee, AddEmployee, AddEmp, sp_InsertEmployee, etc...
(PQ): SELECT * FROM Employees, SELECT * FROM Emps, SELECT * FROM Ovdim (yuck!), SELECT * FROM Subordinates, etc...
(So far he is only limited by the user permissions, we assume that the administrator is not going to check the logs in the weekend).
He successuflly manage to map the following items (using the errors he gets from the database) and their schema:
(SP): AddEmployee, RemoveEmployee. UpdateEmployee, GetEmployee - Stored procedures, only the GetEmployees is avialable to the user he is running under
(PQ): Employees table - Only a select permission.
Using PQ here gave the attacer an easier access to the employees table, since all he needs to do is SELECT * FROM Employees.
The second approach requires a more complex query, but basically it's something like:
WHILE 1=1 BEGIN
SET @current = @current + 1
Not that different, and it is likely that a matching ListEmployees procedure will exist (and that the attacker will have access to it).
Now our nefarious attacker got all the employees data, now he decides to give all the secreteries a 150% salary increase.
Using PQ, he issues the following query:
UPDATE Salary = Salary * 1.5 WHERE Title = 'Secretery'
Using SP, he has a lot more work to do but it's basically the same thing, only procedurally.
If title = 'secretery'
exec UpdateEmployee( ... )
SP: He gets an error because he doesn't have permissions to the procedure.
QP: He gets an error because he doesn't have permissions to the table.
Repeat for inserts / deletes. Different methods, same end result.
I don't see how using Stored Procedures leads to inhernetly better security in this cases.