Using parameterised SQL statements (as in the shown code) can cause SQL to not use indexes, thus causing table scans, which can cause table locks.
@Fran, this might have been true before parameter peeking was introduced in IIRC MSSQL7, or if you way out of date statistics.
My experience is that SQLServer is quite aggressive on using indexes for equals / like% queries (as you would expect) but it is unlikely to use indexes when using a range scan or if you are issuing SELECT * which means both the index and the table row records need to be read.
As always, don’t ASSUME anything. If you want to be sure then design a test case and PROVE it. (I did this on a StackOverflow thread and surprised myself!)