SQL Server Query filter query - NULL parameter

Question

SQL Server database question.

Table schema:

CREATE TABLE [dbo].[TestTable]
(
    [ID] [INT] NOT NULL,
    [PhaseID] [INT] NULL
) ON [PRIMARY]

Data:

INSERT INTO TestTable 
VALUES (1, NULL), (2, 1), (3, 2), (4, NULL)

I am running a SQL query which is supposed to retrieve the record which matches the PhaseId column exactly (it could be null or an integer).. but seem to be missing something.

DECLARE @ID INT, @PhaseID INT

SET @ID = 1
SET @PhaseID = 1

SELECT * 
FROM TestTable
WHERE PhaseID = @PhaseID OR @PhaseID IS NULL

If @PhaseID parameter is integer this works fine.

DECLARE @PhaseID INT 
SET @PhaseID = 1 --works

But if @PhaseID is null, it returns all the records whereas I need the query to return only the first record & 4th record.

DECLARE @PhaseID INT 
SET @PhaseID = NULL

SELECT * 
FROM TestTable
WHERE PhaseID = @PhaseID OR @PhaseID IS NULL -- does not work returns all 4 records

How can this be achieved please?

I have referred a few other questions already but have not been successful. SQL Query Where Clause for Null OR Match (only return 1)?

Check the column not the variable: Where PhaseID IS NULL not Where @PhaseID IS NULL — Alex K.
– Alex K., Commented Oct 2, 2018 at 16:14

Yogesh Sharma · Accepted Answer · 2018-10-02 16:40:41Z

1

You need PhaseID instead of @PhaseID :

Select * 
from TestTable
Where (PhaseID = @PhaseID OR PhaseID IS NULL);

However, i would over thinking with :

. . .
WHERE (@PhaseID IS NOT NULL AND PhaseID = @PhaseID) OR
      (@PhaseID IS NULL AND PhaseID IS NULL);

edited Oct 2, 2018 at 16:40

answered Oct 2, 2018 at 16:14

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

1 Comment

Sri Over a year ago

(@PhaseID IS NOT NULL AND PhaseID = @PhaseID) OR (@PhaseID IS NULL AND PhaseID IS NULL) works perfectly :)

Derrick Moeller · Accepted Answer · 2018-10-02 17:01:40Z

1

You can do this with Dynamic SQL.

DECLARE @PhaseID INT
SET @PhaseID = 1

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'SELECT * FROM TestTable WHERE '

IF @PhaseID IS NOT NULL
SET @Sql = @Sql + 'PhaseID = @_PhaseID'
ELSE
SET @Sql = @Sql + 'PhaseID IS NULL'

EXEC sp_executesql @Sql,N'@_PhaseID int',@_PhaseID = @PhaseID

answered Oct 2, 2018 at 17:01

Derrick Moeller

5,0502 gold badges27 silver badges49 bronze badges

Comments

arahman · Accepted Answer · 2018-10-02 16:16:45Z

-1

Currently you are doing @PhaseID is NULL which brings back 4 rows because you are referencing the parameter for the IS NULL check.

Declare @PhaseID int
SET @PhaseID = NULL

Select * 
from #Data
Where PhaseID = @PhaseID OR @PhaseID IS NULL

This brought back row 1 & 4 by using PhaseID as the column reference rather than referencing the parameter.

Select * 
from #Data
Where PhaseID = @PhaseID OR PhaseID IS NULL

edited Oct 2, 2018 at 16:16

answered Oct 2, 2018 at 16:14

arahman

5852 silver badges9 bronze badges

3 Comments

Alex K. Over a year ago

But op wants 2 rows (1 & 4)

arahman Over a year ago

@AlexK. Amended the answer. Cheers.

Derrick Moeller Over a year ago

This isn't going to work, you're going to get the NULL records when @PhaseID is set to an integer.

Collectives™ on Stack Overflow

SQL Server Query filter query - NULL parameter

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related