Parameters with Wildcards

Be careful not to fall into the trap of adding the wildcard to the sql. It needs to be added to the parameter…

SELECT * FROM tblCustomers WHERE LastName LIKE '%mit%'

will return rows where the LastName field contains “mit”, at the beginning, end or anywhere with in the last name as there are 2 wildcards.

If you want to be able to specify the search criteria at run time, i.e. not just “mit”, replace the hard coded search with a parameter like @Search…

SELECT * FROM tblCustomers WHERE LastName LIKE '%@Search%'

If you are using something like a Sqlcommand in asp.net you will supply the parameter to it through perhaps a TextBox control so you would think you should do this…

Cmd.Parameters.Add("@Search", SqlDbType.NvarChar).Value = TextBoxSearch.Text

BUT THIS IS WRONG AND WON’T WORK!!!

The sql should not contain the wildcards. It should be :

SELECT * FROM tblCustomers WHERE LastName LIKE @Search

and the parameter should have the the wildcards:

Cmd.Parameters.Add(“@Search”, SqlDbType.NvarChar).Value = “%” + TextBoxSearch.Text + “%”