Parameterised query breaks when doing LIKE condition. Why?

Question

So, I have a method that performs a parametrised LIKE query. The method takes in the search parameter/value in and then it is added to the command ready for the query.

It is not working. It should work, and when I code the value to search for directly into the SQL string, sans parametrisation, it does work! When I have it as a parameter is does not! Any ideas.

Here is some (fake, I have changed the names) example code.

myDataReader = SQLExecute("SELECT * FROM TableOfAwesomeness WHERE BestTVShow LIKE 'Arrested Development%'")

Would work. However

Function MethodOfReturningHorror(ByVal TVShow as String) as SqlDataReader
{
dim command as new SQLCommand
command.connection = sqlconnection
command.CommandText = "SELECT * FROM TableOfAwesomeness WHERE BestTVShow LIKE '@tvShow%'"
command.Parameters.Add("tvShow",TVShow)

return command.ExecuteReader()
}

I have missed out code unrelated to the question for the sake of laziness/conciseness. So ignore the return bit and and stuff, all that is important is that the data reader contains nothing, while It does in the first example. I am sure it's to do with the parametrisation of the LIKE clause .

Thanks!

Answer 1

Try this:

command.CommandText = "SELECT * FROM TableOfAwesomeness WHERE BestTVShow LIKE @tvShow + '%'"

Answer 2

Try appending the '%' to the end of the parameter string rather than embedding it in the sql.

Answer 3

@tvShow is a variable and you're using it inside of a string. It would be the equivalent of this in C#:

var tvShow = "5th Wheel";
var netwokAndShow = "Fox tvShow";
Console.WriteLine(networkAndShow); // Prints 'Fox tvShow', not 'Fox 5th Wheel'

You want it to look like:

LIKE @tvShow + '%'

Answer 4

Try this, to ensure that the value that you're checking against is a varchar value, and not say an integer type:

command.CommandText = "SELECT * FROM TableOfAwesomeness WHERE BestTVShow LIKE convert(varchar(100), @tvShow) + '%'"

I've had some problems when T-SQL does native type conversion and string concatenation.

(Obviously change "varchar(100)" to be whatever works in your instance: your data types, your expected length)