Geek Answers Handbook

Sql stored procedure as an operator variable

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

Hello to all. This is a simple question, but I could not understand, since I am pretty new to tsql and sql in general.

I use the above stored procedure to search. My question is about "% field%". Which variable are you using or how does it work in tsql? for example, "where Customers = @CustomerNo." how about a template? how do you pass the variable along with the template? I think I can do the "%" + "field" + "%" in the code, but is there a way to not do this?

+4
source share
3 answers

Wildcards are just part of a string literal, for example. '% field%' is just a string.

You can combine wildcards into your string, and then use the string:

 @Pattern = '%' + @CustomerName + '%'; ...WHERE CustomerName LIKE @Pattern

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

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

There is no other magic solution for this.

+17
source

It is very simple. "=" and "Like" are both operators. What can you do after you can do it after another.

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();
+2
source

If you use =, you say "equals" which will not use wildcards.

If you use LIKE, which only works in text fields, it can use wildcards.

Cannot get wildcard matches with =.

Please note that depending on the data, a pattern search may perform a table scan, so I have to make sure you want it before allowing it.

For example, this will perform a table scan:

 WHERE CustomerID LIKE '%1'

that is, all clients that have a client identifier (which is text) that ends with 1. This cannot be resolved using the index.

Final thoughts. I am not 100% sure, I understand exactly what you are asking. Could you clarify. What exactly do you mean by the word "pass a variable with a wildcard"?

0
source

More articles:


All Articles