Conditional where clause?

Question

I want to apply the conditional where clause That is if my barcode parameter comes null then i want to fetch all the records and if it comes with value then i want to fetch only matching records for the second part i am able to fetch the matching records but i am stuck at fetching the all records in case of null value i have tried as below ,

 SELECT item 
 FROM tempTable
 WHERE 
  ((ISNULL(@barcode,0)=1)
      // but this is not fetching all the records if barcode is null 
   OR
   ISNULL(@barcode,0!= 1 AND tempTable.barcode LIKE @barcode+'%'))
      //THis is working perfect 

so any help will be great

Answer 1

IF @barcode is null

begin

SELECT item FROM tempTable

end

else SELECT item FROM tempTable where tempTable.barcode LIKE @barcode+'%'

Answer 2

Apart of ppeterka's solution (which will causes an Index/Table Scan) there are at least three other solutions. These solutions could use an Index Seek if @barcode isn't NULL and, also, if there is an index on barcode column:

Solution #2: The execution plan isn't cached and reused:

SELECT item 
FROM tempTable
WHERE @barcode IS NULL OR tempTable.barcode LIKE @barcode+'%'
OPTION(RECOMPILE);

Solution #3: The execution plan is cached (it can be used if the num. of optional parameters is small):

IF @barcode IS NULL
    SELECT item 
    FROM tempTable;
ELSE
    SELECT item 
    FROM tempTable
    WHERE tempTable.barcode LIKE @barcode+'%';

Solution #4: The execution plans are cached (it can be used if the num. of optional parameters is high):

DECLARE @SqlStatement NVARCHAR(MAX);
SET @SqlStatement = N'
SELECT item 
FROM tempTable
WHERE 1=1 '
+ CASE WHEN @barcode IS NULL THEN N'' ELSE N'AND tempTable.barcode LIKE @pBarcode+''%''; ' END; 
-- + CASE WHEN @anotherparam IS NULL THEN N'' ELSE 'AND ...' END ;

EXEC sp_executesql @SqlStatement, N'@pBarcode VARCHAR(10)', @pBarcode = @barcode;

Note: Use the proper type and max. lenght/precision & scale for @pBarcode parameter.

Answer 3

If the barcode field is non-null, then this is the method I would use -

SELECT item 
FROM tempTable
WHERE barcode like isnull(@barcode, barcode) + '%'

If @barcode is null all records are returned and if it is non null then only matching records are returned.

If the barcode field is nullable then -

SELECT item 
FROM tempTable
WHERE isnull(barcode, '') like isnull(@barcode, isnull(barcode, '')) + '%'

Same as the first but here we convert the null values in the barcode field to blank strings before doing the compare.

Answer 4

If I have to do this, I would have done like

SELECT item 
 FROM tempTable
 WHERE 
( ( ISNULL(@barcode,'') <> '')  AND ( tempTable.barcode LIKE @barcode+'%' ) )

( ISNULL(@barcode,'') <> '') would also check if the variable is blank then it should not return anything. But if you just check for null, then in case when the @barcode is blank, you will be getting all item selected from the tempTable.

Answer 5

If column barcode would be non-nullable, you could greatly simplify the query. This is based on the fact that the pattern '%' matches any string; even an empty (i.e. zero-length) string. Consequently, the following WHERE clause matches all records:

WHERE barcode LIKE '%'

You may notice that this has a very close resemblance to the WHERE clause you are using to filter records on a specific barcode:

WHERE barcode LIKE @barcode + '%'

In fact, they are so similar that we may as well use a single WHERE clause for both cases; after all, '' + '%' equals '%'!

IF @barcode IS NULL SET @barcode = ''
SELECT item FROM tempTable WHERE barcode LIKE @barcode + '%'

There is an even shorter version, which preserves the original value of @barcode:

SELECT item FROM tempTable WHERE barcode LIKE ISNULL(@barcode, '') + '%'

As mentioned earlier, this works only if column barcode is non-nullable. If column barcode is nullable (and you are genuinely interested in records where barcode IS NULL), then the following query might work for you:

SELECT item FROM tempTable
WHERE ISNULL(barcode, '') LIKE ISNULL(@barcode, '') + '%'

However, this version has two disadvantages:

• It may perform much slower, because the query optimizer may not benefit from an index on column barcode.
• If @barcode = '', then it will match not only the non-null barcodes, but also the records with barcode IS NULL; whether this is acceptable, is up to you.

One last simplification: you may want to reach consensus with the outside world that they should set @barcode = '' instead of NULL to retrieve all records. Then you could replace ISNULL(@barcode, '') by @barcode.

Answer 6

An alternate answer and an attempt at the bounty

declare @barcode nvarchar(10) -- chose nvarchar not necessarily should be nvarchar
select @barcode= NULL
--select @barcode='XYZ'
if @barcode is null
select item from temptable;
else
select item from temptable where temptable.barcode like @barcode+'%';

Answer 7

I might have misunderstood what you ask, but the logic OR operator might help:

SELECT item 
 FROM tempTable
 WHERE 
 @barcode IS NULL OR tempTable.barcode LIKE @barcode+'%'

If @barcode is NULL, it returns all the records, and when it is not NULL, it returns all of the records that fulfill the condition LIKE @barcode+'%'

Important

Also, bear in mind that using the OR operator can seemingly cause funny results when used with several complex conditions AND-ed together, and not enclosed properly in braces:

<A> AND <B> AND <C> OR <D> AND <E> AND <F>

Should most likely actually be formulated as:

(<A> AND <B> AND <C>) OR (<D> AND <E> AND <F>)

Remember, the parser does not know what you want to achieve, you have to describe your intents properly...

Answer 8

I think you could simplify it to:

  SELECT item 
 FROM tempTable
 WHERE @barcode IS NULL OR tempTable.barcode LIKE @barcode+'%'

so when @barcode is null you'll get everything - i.e. the Like part of the where won't need to execute. If @barcode has a value then the Like will be executed.