Filter by IS NOT NULL AND in sql

Question

I have two almost identical records. One that contains a null value for "Request ID" and the other one 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.

Ex:
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:

USE [ResourceRequest]
GO
    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[search_project_bykeyword] 
(@ProjectName varchar(100)=NULL
,@Originator varchar(100)=NULL)
AS
SELECT *
FROM [dbo].[Resource_Request]
WHERE [Request ID] IS NOT NULL
AND 
[Project Name] LIKE @ProjectName + '%'
OR [Originator] LIKE @Originator + '%'
  

Answer 1

AND takes precedence over OR. You should group your AND and OR statements:

SELECT   *
FROM     [dbo].[Resource_Request]
WHERE    [Request ID] IS NOT NULL
AND 
(
         [Project Name] LIKE @ProjectName + '%'
     OR  [Originator]   LIKE @Originator + '%'
)

The reason for the grouping is due to the precedence order mentioned above. This order is:

Level   Operators
1       ~ (Bitwise NOT)
2       * (Multiply), / (Division), % (Modulo)
3       + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
4       =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5       NOT
6       AND
7       ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
8       = (Assignment)

What this means is, all of your AND statements will be evaluated first. After they have all been evaluated, then your OR statement is evaluated.

This makes your query look more like this to the compiler:

WHERE  
(   
       [Request ID] IS NOT NULL
   AND [Project Name] LIKE @ProjectName + '%'
)
OR     [Originator] LIKE @Originator + '%'

Which isn't the clause you were looking for.

By grouping the [Project Name] and [Originator] segments of your WHERE clause, you are telling the compiler that these two should be evaluated as a set, and using an AND between that set and the [Request ID] portion keeps it at the same precedence level.