This post originated from an RSS feed registered with .NET Buzz by Peter van Ooijen. Original Post: SELECT LIKE % Feed Title: Peter's Gekko Feed URL: /error.htm?aspxerrorpath=/blogs/peter.van.ooijen/rss.aspx Feed Description: My weblog cotains tips tricks and opinions on ASP.NET, tablet PC's and tech in general. Latest .NET Buzz Posts Latest .NET Buzz Posts by Peter van Ooijen Latest Posts From Peter's Gekko

Trying to be smart I ran into this. In an app the user is looking for some person, the query has some parameters to help her.

SELECT Naam, Afkorting FROM  dbo.Persoon WHERE (Naam LIKE @Param1) AND (Functie LIKE @Param2)

To the parameter values is appended the % character.  The query will return all rows which start with the value supplied.

PersoneelZoeken.SelectCommand.Parameters["@Param2"].Value = string.Format("{0}%", paramvalue);

The catch is what happens when an empty parameter is passed in. Which will result in a query which looks like

SELECT Naam, Afkorting FROM  dbo.Persoon WHERE (Naam LIKE '%') AND (Functie LIKE '%')

At first sight the query will return all rows. It does not. It will return al rows which do contain a value for the Naam and the Functie column. % matches any text but does not match a dbNull value. Makes sense. But was a catch.