Phew, just spent some time trying to get a parameter to work with a LIKE and wildcards in the WHERE clause of a SELECT. I’m creating a stored procedure for SQL Server 2000.
For example, I wanted something like this:
SELECT * FROM table WHERE no_column_in_particular LIKE ‘%@param%’
But it would always return all the rows, so I knew that I was getting doing something like “LIKE ‘%%’” I tried all sorts of different methods like wrapping the parameter in brackets. I finally found the answer in this SQL Server Magazine forum entry.
The solution is to use string concatenation or add it to the parameter before running the SELECT:
SELECT * FROM table WHERE no_column_in_particular LIKE ‘%’ + @param + ‘%’
Or:
SET @param = ‘%’ + @param + ‘%’
I’m not sure if the former is considered dynamic SQL in crafting the plan, but if it is then the latter might be higher performing. IANADBA.