Question: 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](https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(ANSI_NULLS_TSQL);k(SQL12.SWB.TSQLRESULTS.F1);k(SQL12.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true) 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.

Answer: 
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.
Here is an elegant and concise way to code this:

```
SELECT *
FROM Categories
WHERE COALESCE(PID, 'NULL-MATCH') = COALESCE(@parentID, 'NULL-MATCH')

```