You coulduse LIKE clause with or without a wild card. For example,
-- this is perfectly valid and correct
SELECT Customer FROM Tbl WHERE CUSTOMER LIKE 'Smith'
It is good that you are revising the code to avoid SQL Injection and parameterizing the query. One thing to note though, is that when you do that, sometimes you can run into a problem that is often referred to as parameter sniffing. This happens when SQL Server generates an optimized execution plan based on one set of parameters, but that plan turns out to be completely inefficient for another set of parameters. As an example, consider the two queries below:
SELECT Customer FROM Tbl WHERE Customer LIKE '%Smith';
SELECT Customer FROM Tbl WHERE Customer LIKE 'Smith%'The query plan for the first select would not be able to use any index that may be on Customer column. If the query plan generated for that select is reused for the second query, it would miss out on the opportunity to use the index even though the second query could make use of such an index. If you run into that problem, you may find this article interesting read: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/