To demonstrate the advantages of using a stored procedure over embedded SQL, try a basic SQL Injection attack against our logon screen.
user name: foo' OR 1=1--
with no password
This simple SQL Injection attack will give you access to the site, even though we did not supply a valid user name and password.
Now use this small attack against our logon.asp page utilizing the stored procedure to access the database. You will have to modify the logon.asp page to call processlogon_sp.asp instead of processlogon.asp.
user name: foo' OR 1=1--
with no password
As you can tell from the results return by the logon.asp page, parameterized stored procedures can go a long way in protecting your database applications from SQL Injection. Given no input validation, the parameterized stored procedure still does not allow you to gain access to the site. As stated above, I would not drop all validation from your inputs; just add parameterized stored procedures as a second layer of protection.
Granted the example attack was not very complex but it is enough to show you how dangerous embedded SQL statements can be and how simple it is to create a stored procedure to validate users with your standard logon screen.