Microsoft® SQL Server 2008 R2 Unleashed

Using Wildcards in Parameters

Wildcards can be included in varchar-based input parameters and used in a LIKE clause in a query to perform pattern matching. However, you should not use the char data type for parameters that will contain wildcard characters because SQL Server pads spaces onto the value passed in to the parameter to expand it to the specified size of the char data type. For example, if you declared an @lastname parameter as char(40) and passed in 'S%', SQL Server would search not for a string starting with 'S' but for a string starting with 'S', any characters, and ending with up to 38 spaces. This would likely not match any actual data values.

Also, to increase the flexibility of a stored procedure that searches for character ...