thatdude thatdude - 4 months ago 10
SQL Question

Filter by IS NOT NULL AND in sql

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.

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

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 + '%'.