Sql store procedure Like Operator variable

Question

print("select CustomerNo, CustomerName, Address, City, State, Zip,
    Phone, Fax, ContactName, Email  
    from Customers where CustomerName like '%field%'");

Hi all. This is a simple question but I wasn't able to figure since I'm pretty new to tsql and sql in general.

I use the above store procedure to do search. My question is for '%field%'. What variable do you use or how does it work in tsql? for example, "where Customers = @CustomerNo". how about for wildcard? how do you pass in a variable along with wildcard? I guess i can do "%" + "field" + "%" in the code but is there a way not to do that?

Answer 1

If you use =, you say "equal", which won't use wildcards.

If you use LIKE, which only work on text fields, it can use wildcards.

There is no way you can get wildcard matches with =.

Note that depending on the data, a wildcard search might do a table-scan, so I would make sure that's what you want before you allow it.

For instance, this will do a table-scan:

WHERE CustomerID LIKE '%1'

ie. all customers who have a customer identifier (which is text) that ends with a 1. That can't be solved with an index.

Final thoughts. I'm not 100% sure I understand exactly what you're asking. Could you please clarify. What specifically do you mean by "pass in a variable along with wildcard"?

Answer 2

Wildcards are simply part of a string literal, e.g. '%field%' is just a string.

You can concatenate the wildcards onto your string and then use the string:

@Pattern = '%' + @CustomerName + '%';

...WHERE CustomerName LIKE @Pattern

Or else you can write an expression in the SQL involving concatenation:

WHERE CustomerName LIKE '%' + @CustomerName + '%'

There's no other magic solution for this.

Answer 3

First off, that there is whatcha call a query not a stored procedure...but that's another post.

You'll need to concatenate the parameter and the wildcard operators:

select CustomerNo, CustomerName, Address, City, State, Zip,
    Phone, Fax, ContactName, Email  
    from Customers where CustomerName like '%' + @MyParameter + '%'

Answer 4

It is very simple. "=" and "Like" are both operators. What you can do after one you can do after the other.

So, if in C# and using SQLClient calls, you can say:

string value;
...
value = "Some name";
...
myCommand.CommandText = "Select...from Customers Where CustomerName Like @Var";
myCommand.Parameters.AddWithValue("@Var", "%" + value + "%");
myCommand.ExecuteNonQuery();

Answer 5

thax! :) concatenation does solved my problem.