Bill Brown bio photo

Bill Brown

A complicated man.

Twitter Github

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 + ‘%’


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.