CODEWITHSUNDEEP
sqlsql-servert-sql
Casey Crookston
Casey Crookston

Reputation: 13945

IS NULL vs = NULL.... How to avoid a huge IF ELSE?

A scaled down look at my query looks like this:

SELECT 
    ...
FROM
    Settings s
    LEFT JOIN OrganizationUserSettings ous ON s.SettingID = ous.SettingID 
        AND (ProfileID = @ProfileID OR ProfileID IS NULL)

Now, in the OrganizationUserSettings table I have two row that look like this:

ID    SettingID    ProfileID 
----------------------------
1        3           NULL
2        3           50

So as would be expected, in my results I am getting both rows. But I need to be getting only one row. If there is a match for ProfileID = @ProfileID, then I need that one. If there isn't, then I'll take the one that's NULL.

Or, better yet... in short, here's my real problem. I could do a giant IF ELSE like this: 

IF (@ProfileSys IS NULL)
BEGIN
    SELECT 
        ...
    FROM
        Settings s
        LEFT JOIN OrganizationUserSettings ous ON s.SettingID = ous.SettingID 
            AND (ProfileID IS NULL)
END
ELSE
BEGIN
        SELECT 
        ...
    FROM
        Settings s
        LEFT JOIN OrganizationUserSettings ous ON s.SettingID = ous.SettingID 
            AND (ProfileID = @ProfileID)
END

But I REALLY don't want to have to do that. What I'm showing you here is just part of a much larger query (I've scaled it down here for simplicity sake.) And I don't want to have to make a fully dynamic query.

Is there a way to do this in one line of the original JOIN?

Thanks!

Upvotes: 1

Views: 74

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Unless I'm missing something here, there's a simple solution:

SELECT 
...
FROM
Settings s
LEFT JOIN OrganizationUserSettings ous ON s.SettingID = ous.SettingID 
    AND (ProfileID = @ProfileID OR (ProfileID IS NULL AND @ProfileID IS NULL))

Upvotes: 6

Related Questions