People say to prevent SQL Injection, you can do one of the following (amongst other things):
I have done item 1, preparing my statements, but I'm now wondering if I should escape all user input as well. Is this a waste of time seeming as I have prepared statements or will this double my chances of prevention?
Certainly the first step to prevent SQL Injection attacks is to always use parameterised queries, never concatenate client supplied text into a SQL string. The use of stored procedures is irrelevant once you have taken the step to parameterise.
However there is a secondary source of SQL injection where SQL code itself (usually in an SP) will have to compose some SQL which is then EXEC'd. Hence its still possible to be vunerable to injection even though your ASP code is always using parameterised queries. If you can be certain that none of your SQL does that and will never do that then you are fairly safe from SQL Injection. Depending on what you are doing and what version to SQL Server you are using there are occasions where SQL compositing SQL is unavoidable.
With the above in mind a robust approach may require that your code examines incoming string data for patterns of SQL. This can be fairly intense work because attackers can get quite sophisticated in avoiding SQL pattern detection. Even if you feel the SQL you are using is not vunerable it useful to be able to detect such attempts even if they fail. Being able to pick that up and record additional information about the HTTP requests carrying the attempt is good.
Escaping is the most robust approach, in that case though all the code that uses the data in you database must understand the escaping mechanim and be able to unescape the data to make use of it. Imagine a Server-side report generating tool for example, would need to unescape database fields before including them in reports.
Server.HTMLEncode prevents a different form of Injection. Without it an attacker could inject HTML (include javascript) into the output of your site. For example, imagine a shop front application that allowed customers to review products for other customers to read. A malicious "customer" could inject some HTML that might allow them to gather information about other real customers who read their "review" of a popular product.
Hence always use Server.HTMLEncode on all string data retrieved from a database.