1

The title only explains my first attempt, I couldn't think how to word the issue/my need exactly.

I have table job:

CREATE TABLE [dbo].[job] 
(
    [jobId]      INT IDENTITY(1,1) PRIMARY KEY,
    [contractId] INT NOT NULL,
    [districtId] INT NULL,
    [address]    NVARCHAR (255) NULL
);

I'm trying to create a function where all of the column values can be optionally passed through to do a filter search.

So far I have:

DECLARE
    @jobId          INT,
    @contracId      INT,
    @districtId     INT,
    @addressPart    NVARCHAR(255)

SELECT 
    [jobId],
    [contractId],
    [districtId],
    [address]   
FROM
    [dbo].job
WHERE 
    jobId = ISNULL(@jobId,jobId)
    AND contractId = ISNULL(@contracId, contractId)
    AND [address] LIKE '%' + ISNULL(@addressPart, [address]) + '%'

This works, however because the districtId can be null in the Job table doing the same ISNULL where clause as for contractId results in the query to only ever return records where districtId is not null, as the '=' condition doesn't work for null values.

I've tried using COALESCE

AND COALESCE(districtId, 0) = COALESCE(@districtId, 0)

But that results in the query only selecting records where districtId is null if no districtId is specified. It does select the the row with the districtId if a 'district' is set. But this is ultimately no good as if any other variable is set which would match a record, if that record has null districtId it won't be selected.

I've also tried using OR

AND (districtId = ISNULL(@districtId, districtId) OR districtId IS NULL)

But this returns everything when the districtId is null, but then if a districtId is set, it'll get that record, but also still return all records that have districtId null.

I can't even think how to do this with a dirty CASE statement, as I'd need to do districtId = and as soon as I do that '=' its not going to work as it need to on some level check for null is null which '=' can't do.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vereonix
  • 1,341
  • 5
  • 27
  • 54
  • 1
    From your question it's not clear to me what you want to get back for the four combinations of `@districtId` is/is not `NULL` and `districtId` is/is not `NULL`. In particular, it's not clear to me if you want to search for rows with `districtId` `NULL` and if so, when. (Meanwhile, I have [this article](https://sommarskog.se/dyn-search.html) for information on how to do dynamic search conditions "right" in terms of performance -- [`ISNULL` isn't it](https://sommarskog.se/dyn-search.html#coalesce).) – Jeroen Mostert May 31 '21 at 16:22
  • I want the function to be a search, where any combination of the variables can be passed and records which match are returned. So default the 'districtId' is null so the query need to return all records regardless of what the records 'districtId' is. But if a 'districtId' is passed then I need to select the record/s with that id. – Vereonix May 31 '21 at 16:27
  • 1
    So `districtId = @districtId OR @districtId IS NULL`, then? Note that the optimizer also likes this formulation a lot better than any calculated expressions involving `ISNULL`/`COALESCE` (especially if `OPTION (RECOMPILE)` is used). – Jeroen Mostert May 31 '21 at 16:29
  • 1
    Catch-all queries have bad performance whether you write them as `(@jobId is null or jobid=@jobid)` or `jobid=(@jobid,jobid)`. Which won't work anyway, because `NULL != NULL` – Panagiotis Kanavos May 31 '21 at 16:31

2 Answers2

3

You must check separately if @districtId is null in which case the condition should return true:

AND (@districtId IS NULL OR districtId = @districtId)
forpas
  • 160,666
  • 10
  • 38
  • 76
0

You still need to include the column in the COALESCE to handle the param = column value

 AND COALESCE(districtId ,0) = COALESCE(@districtId, districtId,0)
Richard Hubley
  • 2,180
  • 22
  • 29