-1

I have this statement in my where clause:

(A.[subject] LIKE '%'+@meetingSubject+'%')

I only want this to be searched if @meetingSubject is not empty.

As of right now, its returning all records when @meetingSubject is null

How can I accomplish this ?

SBB
  • 8,560
  • 30
  • 108
  • 223
  • possible duplicate of [like '%' does not accept NULL value](http://stackoverflow.com/questions/3924400/like-does-not-accept-null-value) – Danny T. Jun 09 '14 at 19:19
  • I don't think it is a duplicate since OP says it returns all records and it shouldn't. – Patrick Hofman Jun 09 '14 at 19:22
  • That's very strange. You should get no rows at all when `@meetingSubject` is `NULL`, unless you have ANSI null handling turned off. Do you mean that there is a `null` in your front-end application code? If so, it's NOT being translated to `NULL` when stuffed into parameter `@meetingSubject`. – ErikE Jun 09 '14 at 19:25
  • Please clarify if it's a NULL and you have a database option set to defeat normal ANSI NULL handling, or whether it's really an empty string. – ErikE Jun 09 '14 at 19:51
  • If you only want it to be searched when provided, you need to remove the condition altogether - that is, you want dynamic SQL. SQL Server in particular has problems with 'optional' parameters like this. – Clockwork-Muse Jun 10 '14 at 10:58

5 Answers5

2

The question says "empty" and not "NULL". If the value were NULL, the expression would fail and no records would be returned. That is not the behavior the OP describes. Hence, I think you arelooking for:

(A.[subject] LIKE '%'+@meetingSubject+'%' AND @meetingSubject <> '')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • THIS is the right answer. Obviously the `@meetingSubject` parameter is not set to `NULL` but an empty string `''`. – ErikE Jun 09 '14 at 19:27
  • The question uses both empty and null. By the way, why not checking the parameter instead of the field for empty? – Patrick Hofman Jun 09 '14 at 19:37
  • @PatrickHofman Okay, that's a good point about the parameter vs. the field. – ErikE Jun 09 '14 at 19:49
  • I don't think the question is using null and empty. It using both terms but I don't think it is using both. – paparazzo Jun 09 '14 at 20:13
1

case also exclude an empty string not only when is NULL

(ISNULL(@meetingSubject, '') <> '' and A.[subject] LIKE '%'+@meetingSubject+'%')
robertw
  • 724
  • 7
  • 20
  • This makes no sense. If `@meetingSubject` is `NULL`, it would return no rows, anyway, so the only part of your code actually doing something different is the check for no empty string. – ErikE Jun 09 '14 at 19:32
  • @ErikE your right if option CONCAT_NULL_YIELDS_NULL is set to default. If not, concatenation return empty string. From another side interesting how the data looks like. – robertw Jun 09 '14 at 19:36
0

http://msdn.microsoft.com/en-us/library/ms188795.aspx

This might do what you want:

 ( @meetingSubject IS NOT NULL and @meetingSubject != '' and A.[subject] LIKE '%'+@meetingSubject+'%')
iewebguy
  • 316
  • 3
  • 16
  • Switch the conditions. It will make the execution faster. – Patrick Hofman Jun 09 '14 at 19:19
  • Same issue using this code, it is returning all data when subject is empty. – SBB Jun 09 '14 at 19:21
  • This makes no sense. If `@meetingSubject` is `NULL`, it would return no rows, anyway, so the only part of your code actually doing something different is the check for no empty string. – ErikE Jun 09 '14 at 19:27
0

This should be a cleaner approach, though Gordon Linoff answer would work as well

A.[Subject] like '%' + NULLIF(@meetingSubject, '') + '%'

Cleaner in the since of readability.

Community
  • 1
  • 1
Dbloch
  • 2,326
  • 1
  • 13
  • 15
-2

I don't think @meetingSubject is null
I think @meetingSubject is ''
It is not the same in TSQL

That first @var = null; does NOT return found.

declare @var varchar(100)
declare @search varchar(100)
set @search = 'searchvaluesting'
set @var = null;
select '%' + @var + '%';
select 'found' where @search like '%' + @var + '%';  -- returns nothing
select 'found' where @search like '%' + @var + '%' and @var <> '';  -- returns nothing
set @var = '';
select '%' + @var + '%';
select 'found' where @search like '%' + @var + '%' and @var <> '';  -- returns nothing
set @var = '  ';
select '%' + @var + '%', LEN(@var);
select 'found' where @search like '%' + @var + '%' and @var <> '';  -- returns nothing
set @var = 'value';
select '%' + @var + '%';
select 'found' where @search like '%' + @var + '%' and @var <> '';  -- returns found
paparazzo
  • 44,497
  • 23
  • 105
  • 176