I have two almost identical records. One that contains a null value for "Request ID" and one that doesn't. I want to filter out the one that doesn't have a null value for that column but I am also looking to filter out the data based on a keyword.
Search: ProjectName or Originator
Result: get the entry without the null "Request ID" value back
Searching for the ProjectName gets me what I need but searching for Originator still gives me both results?
Here is what I have so far:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[search_project_bykeyword]
WHERE [Request ID] IS NOT NULL
[Project Name] LIKE @ProjectName + '%'
OR [Originator] LIKE @Originator + '%'
Just put parenthesis around this part, and you should be fine:
( [Project Name] LIKE @ProjectName + '%' OR [Originator] LIKE @Originator + '%' )
The reason for your problem was that the
OR makes your filter true for any rows that match the
Originator filter, regardless of the other filters, so the
[Request ID] IS NOT NULL filter gets ignored as long as
[Originator] LIKE @Originator + '%'.