• 5 Subscribers
  • 114 Views
1

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.


3 answers in total

2
JohnHC Posted at 2017-01-11 15:55:02Z

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
2
MDiesel Posted at 2017-01-11 16:01:40Z

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
0
DVT Posted at 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

Answer this questsion