An interesting issue I came into recently. Suppose you have a search procedure - which consists of some SELECT query which gets a query phrase as a parameter and searches through a table column using LIKE.
The most common pattern is to add % at the beginning and the end of the search parameter so the query looks like so:
WHERE Col LIKE '%' + @param + '%'
Or you may perform concatenation before running the query as well :) However need to take into account the following. The search parameter which usually comes down to the procedure from the application user interface can contain T-SQL reserved wildcard characters such as => % [ ] _ etc. Such input will cause the query to return wrong results.
Therefore it is useful to remember to filter out or replace these characters so MSSQL will consider these characters literals. For example if we have rectangular braces => , need to replace the opening brace => [ with the sequence => [ before running the query.
Thursday, May 08, 2008 2:37 AM