<p>I understand the differences between optimistic and pessimistic locking.  Now, could someone explain to me when I would use either one in general?</p>
<p>And does the answer to this question change depending on whether or not I'm using a stored procedure to perform the query?</p>
<p>But just to check, optimistic means &quot;don't lock the table while reading&quot; and pessimistic means &quot;lock the table while reading.&quot;</p>

Optimistic assumes that nothing's going to change while you're reading it.
Pessimistic assumes that something will and so locks it.
If it's not essential that the data is perfectly read use optimistic. You might get the odd 'dirty' read - but it's far less likely to result in deadlocks and the like.
Most web applications are fine with dirty reads - on the rare occasion the data doesn't exactly tally the next reload does.
For exact data operations (like in many financial transactions) use pessimistic. It's essential that the data is accurately read, with no un-shown changes - the extra locking overhead is worth it.
Oh, and Microsoft SQL server defaults to page locking - basically the row you're reading and a few either side. Row locking is more accurate but much slower. It's often worth setting your transactions to read-committed or no-lock to avoid deadlocks while reading.