CODEWITHSUNDEEP
sql-server
Sri
Sri

Reputation: 155

SQL Server Query filter query - NULL parameter

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)?

Upvotes: 2

Views: 1062

Answers (3)

Derrick Moeller
Derrick Moeller

Reputation: 4950

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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);

Upvotes: 1

arahman
arahman

Reputation: 585

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

Upvotes: -1

Related Questions