SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-26-2008, 05:38 PM
| ||||
| ||||
 Re: SQL Stored Procedure coding question ... Paul Reddin wrote: > Hi, > > In a SQL SP we want to use the following predicate(to allow > for the case where the variable isn't passed/set). > > > WHERE > (PART_NUMBER LIKE v_part_number OR v_part_number IS NULL) > > unfortunately, this has a much higher cost than our previously > used > > WHERE > (PART_NUMBER LIKE v_part_number) > > looking at the plan it appears the OR necessitates a full Index > scan, that the other didn't? > > any ideas on another way to achieve this more effciently? > > Thanks. > > Paul Hi Paul, where part_number like coalesce(v_part_number,'%') I didn't test it out but it's an idea. An alternative is dynamic SQL: if v_part_number is null then sql_stmt = 'where 1 = 1' else sel_stmt = 'where part_number like v_part_number' Guido      |
Linear Mode
