March 3, 2011 at 10:08 am
I need to search a product code field, if nothing is found i want to search the description field like
select id from table where productcode = '1234'
if empty then select id from table where description like '%1234%'
what would be the best way to do this in a stored procedure?
March 3, 2011 at 10:19 am
What's "best" will depend on what you're doing with the data.
A common solution is to insert the results into a temp table, and if @@rowcount = 0, then to insert the alternate query.
Another possibility is a single select statement with an "or" clause that cover the Not Exists condition.
Another is a Union All query with the top query having a Where clause looking for your main item, and the bottom having a Not Exists in its Where clause.
Another is a Union All in a CTE, with both Where clauses, no Not Exists item, and just Select top 1 from the CTE.
There are lots of options. You'll need to try it with your actual data and your actual purpose for the data, to see which is "best".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 7, 2011 at 3:13 am
If you want the fastest possible searches, at the cost of slightly slower inserts/updates/deletes,
do this:
Create an index on productcode.
Create a fulltext index on description. If you do not want to use fulltext index or have edition of sql server that doesn't support that feature, you could use trigger that parses description for numbers and inserts them into separate table (together with PK of your original table) which has clustered index on ParsedNumber column. That mimics fulltext index.
Query first on productcode column that is indexed, and if @@rowcount=0 query your separate table of parsed descriptions.
March 8, 2011 at 12:22 pm
Michael,
If your product table isn't very large I would use GSquared's solution #1. (Insert into a temp table for the actual product code and check @@rowcount). It's very simple and works quite well - I've used it.
The problem is if your product table is large and you have to use the LIKE %SomeWord% type operator. That causes a table scan - no other way around it.
I've found that product descriptions in most large systems have very straight forward descriptions and don't have a lot of garbage punctuation in them that makes the technique I wrote about recently very workable.
Here is a link to the article I wrote on it:
http://www.sqlservercentral.com/articles/String+Manipulation/72540/
This technique won't work for everyone, but it has worked quite well for me for 3 different clients.
Another option, as someone already mentioned, is Full Text Search on the product description, which may or may not be a can of worms you want to open. Please read the comments posted on the article.
The problem in your original post was exactly what I had with the auto parts distributor. The user could enter an actual product code, a bar code or one or more key words to search for. I used GSqared's technique to check for product code. If nothing there then bar code (an indexed column in the table). If nothing there then used my key word search technique.
Todd Fifield
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply