SQL Query - add NOT NULL condition to either of two columns

Question

I am creating a stored procedure in SQL Server as follows:

CREATE PROCEDURE [dbo].[SearchData] 
    @searchText varchar(500)
AS
BEGIN
    SET NOCOUNT ON

    SELECT TOP 50 
        s.[MaxRank] AS [Rank],
        sc.TaskSourceId,
        c.IndividualName,
        c.EntityName,
        c.Text
    FROM
        (SELECT
             MAX(s.Rank) AS MaxRank,
             MAX(c.CaptureId) AS MaxCaptureId,
             c.TaskSourceId
         FROM
             FreeTextTable(Data.SearchData,
                  (Identifier, IndividualName, EntityName, [Text]), @searchText) s
         JOIN
             Data.Capture c ON s.[Key] = c.CaptureId
         GROUP BY
             c.TaskSourceId) s
    JOIN
        Data.Capture c ON c.CaptureId = s.MaxCaptureId
    JOIN
        Data.Source sc ON c.TaskSourceId = sc.TaskSourceId
    ORDER BY
        s.MaxRank DESC
END

The above executes successfully but includes some results where the Text field is the only field with data, with the others being NULL. I want to add a condition that says that, in the returned results, IndividualName must not be null OR EntityName must not be null.

I'm having trouble figuring where to put this and the exact syntax.

In other words Where (IndividualName is not null OR EntityName is not null). Just a SQL translation from what you wrote — Pரதீப்
– Pரதீப், Commented Jan 11, 2017 at 15:58

JohnHC · Accepted Answer · 2017-01-11 15:55:02Z

2

I assume you mean that one of the fields CAN be null. If so:

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text

from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group   by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
where not (c.IndividualName is null and c.EntityName is null) -- excludes results where both are null
order   by s.MaxRank desc
END

answered Jan 11, 2017 at 15:55

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pரதீப் Over a year ago

In other words IndividualName is not null OR EntityName is not null

JohnHC Over a year ago

@Prdp True, but when you start adding other conditions, this is much tidier and less likely to result in confusion.

Pரதீப் Over a year ago

Upvoted.. But I feel many find difficult to understanfd NOT operator when there is more than one condition involved inside NOT

Mark Adelsberger Over a year ago

(InvidiualName is not null OR EntityName is not null) (with the parens) should give best of both worlds, should it not?

MDiesel · Accepted Answer · 2017-01-11 16:01:40Z

2

You can do an isnull on both columns and check to make sure that value is not null, like this:

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text

from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group   by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
where not isnull(c.IndividualName, c.EntityName) is null  
order   by s.MaxRank desc
END

answered Jan 11, 2017 at 16:01

MDiesel

2,65713 silver badges15 bronze badges

4 Comments

Pரதீப் Over a year ago

Cool... never seen a sql construct like that not isnull(c.IndividualName, c.EntityName) is null... +1

JohnHC Over a year ago

While a clever and readable approach, how does this handle over large datasets? Also, sargable?

MDiesel Over a year ago

Good question John. I have used this approach before on pretty large datasets but I cannot say for sure whether it is sargable or not (my guess is that it is not).

Deadsheep39 Over a year ago

It's not SARG (cannot use index on names).

DVT · Accepted Answer · 2017-01-11 15:59:40Z

You can also use these condition in the join condition.

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text
from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId AND (c.IndividualName IS NOT NULL OR c.EntityName IS NOT NULL)
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
order by s.MaxRank desc

END

Collectives™ on Stack Overflow

SQL Query - add NOT NULL condition to either of two columns

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related