SQL Parameters with Like Terms in .Net

Question

I have a pretty complex query and was fixing my like terms (which I have as a separate db table and load them into my search query), but when I parameterize it, my query gives different results.

So the old query had a like section like so:

((v.title = ''BA'') OR (vs.label = ''BA'') OR 
(v.title LIKE ''BA %'') OR (vs.label LIKE ''BA %''))
...

And I replaced it with something like this (with parameters):

((v.title = @banone) OR (vs.label = @banone) OR 
(v.title LIKE @baright) OR (vs.label LIKE @baright))
...
@banone=N'BA',@baright=N'BA %',
...

My parameters get added like so:

string key = "ba"; 
string val = "ba";

parameters.Add("@" + key + "none", val);
parameters.Add("@" + key + "right",  val + " %");

Are these two queries equal? Am I missing something?

Are you really setting the variables after the query or is that just how you posted it? — D Stanley, Jan 03 '13 at 01:11
Shouldn't your second parameter be `@baright=N''BA %''` for the `LIKE` to work right? — JOpuckman, Jan 03 '13 at 01:24
So I am assuming that you are really using BA(uppercase) for val? — Nix, Jan 03 '13 at 01:32
With the parameters.add statement aren't you missing the data type? Have you tried using parameters.AddWithValue? — NotMe, Jan 03 '13 at 01:35
i thought the datatype since its text will default to the varchar of what's being passed in. It looks okay in the sql profiler. — cdub, Jan 03 '13 at 01:45

score 2 · Answer 1 · answered Jan 03 '13 at 01:25

2

(
  (v.title = @banone) OR (vs.label = @banone) OR 
  (v.title LIKE @banone + ' %') OR (vs.label LIKE @banone + ' %')
)
...
@banone=N'BA'
...

answered Jan 03 '13 at 01:25

Nix

57,072
29
149
198

NotMe · Answer 2 · 2013-01-03T01:40:20.863

The first two or cases have no reason to exist. You'll capture the same records if you just use the LIKE cases. For example:

(v.title LIKE @banone + ' %') OR (vs.label LIKE @banone + ' %')

where @banone='BA'

I think your parameter adding code isn't right.

This parameters.Add("@" + key + "right", val + " %");

Should be parameters.AddWithValue("@" + key +"right", val + "%");

There are two things to note. First I removed the space just prior to the percent sign. Second, I'm using AddWithValue instead of a plain Add.

The question back to you then is whether or not that space was important. Looking back at your original query, searching for title = 'BA' or title LIKE 'BA %' would actually ignore things where title had a value of BAT for example but would pickup a title like BA Something

You might want to post the data you are working with and the result you received.

SQL Parameters with Like Terms in .Net

2 Answers2