0

I have a SP that has a very complex SQL statement(s) where I need to be able to compare some column to NULL e.g.

...
FROM Categories
WHERE PID = @parentID

@parentID is a SP parameter which can be valid NULL.
PID (parent ID) is uniqueidentifier which can also be valid NULL (top level category). I could use SET ANSI_NULLS OFF but the documentation says:

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

What can be an elegant way instead of repeating the same query(s) with IS NULL in case @parentID=NULL (and also not using dynamic SQL):

IF @parentID IS NULL 
  SELECT...WHERE PID IS NULL
ELSE
  SELECT...WHERE PID = @parentID

EDIT: I want to avoid an IF because I hate repeating (huge) code.

zig
  • 4,524
  • 1
  • 24
  • 68
  • What's wrong with your `IF...ELSE`? It's clear, simple and efficient – Tim Schmelter Jan 25 '17 at 12:52
  • The `if` method is probably the best method, particularly with `recompile` to ensure that both versions will use an index when the stored procedure is compiled. – Gordon Linoff Jan 25 '17 at 12:52
  • You could always vote for SQL Server to add support for the ANSI [`IS [NOT] DISTINCT FROM`](https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate), but it's been open for ten years and I've not heard even a whisper of it being implemented. – Damien_The_Unbeliever Jan 25 '17 at 13:20

3 Answers3

5

Something like:

select ..
FROM Categories
WHERE PID = @parentID or (PID is null and @parentID is null)
Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
  • Thanks. Would this ignore the index on PID? – zig Jan 26 '17 at 07:18
  • @zig Well, it will not "ignore" index at all (if you have corresponding one). But in SQL Server `or` operator is not a sargable one - so instead of "index seek" you will find "index scan" in execution plan, and this is less efficient than index seek in most cases. If it is performance-critical in your case - you have to use approach suggested by Gordon Linoff or your initial approach using `if` condition. – Andrey Korneyev Jan 26 '17 at 07:23
  • Thanks for the info. it's really not performance-critical in this specific case. this is why I choose your solution, it's clear and short and it works fine for me. I will investigate further on the index. – zig Jan 26 '17 at 07:27
  • BTW, wouldn't it it better to use: `WHERE (PID = @parentID and @parentID is not null) or (PID is null and @parentID is null)`? I assume SQL will shortcut the `PID = @parentID` when `@parentID is null`... – zig Jan 26 '17 at 07:31
  • `PID = @parentID and @parentID is not null` is redundant condition, because when `@parentID` is null first part of condition will never be satisfied and when it is not null - second part is redundant ;) – Andrey Korneyev Jan 26 '17 at 07:33
  • So SQL handles `PID = @parentID` as FALSE immediately when `@parendID` is NULL? there is no scan/seek? – zig Jan 26 '17 at 07:35
  • yes, you can try and see it on some example like `select * from some_table where somecolumn = null` - just run it and look into execution plan. SQL Server query optimizer is smart enough, so if condition will never be satisfied - it will not perform index/table scan or seek. – Andrey Korneyev Jan 26 '17 at 07:47
4

I think the if version is the clearest. A big issue with multiple queries, though, is that the stored procedure compiles the code when it is first run -- and it might make the wrong decision about the execution plan.

One option is to include recompile.

Another is to combine the queries, but in a way where each part should use indexes effectively:

select c.* from categories c where pid is null and @parentid is null
union all
select c.* from categories c where pid = @parentId;

This is a tiny bit less efficient than the if version.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Here is an elegant and concise way to code this:

SELECT *
FROM Categories
WHERE COALESCE(PID, 'NULL-MATCH') = COALESCE(@parentID, 'NULL-MATCH')
stack0114106
  • 8,534
  • 3
  • 13
  • 38
Chris Clark
  • 1