views:

554

answers:

4

When @RadioServiceGroup is set to NULL, I want to return all the records from the sbi_l_radioservicecodes table which has about 120 records. However, when I execute the following procedure and set the @RadioServiceGroup to NULL, it returns no records. Here is the stored proc:

CREATE PROCEDURE [dbo].[GetRadioServiceCodes] 
@RadioServiceGroup nvarchar(1000) = NULL
AS
BEGIN
IF(@RadioServiceGroup = NULL)
    BEGIN
        SELECT rsc.RadioService
        FROM sbi_l_radioservicecodes rsc 
    END
    ELSE
    BEGIN  
        SELECT rsc.RadioService
        FROM sbi_l_radioservicecodes rsc
        WHERE rsc.RadioServiceGroup = @RadioServiceGroup    
    END
END
+6  A: 

Try "IS NULL" instead of "= NULL"

Corey Trager
Wow. Thanks, I feel like a certified idiot. Curious, is it just a syntax thing on why = does not work on nvarchar for NULL
Xaisoft
By definition, NULL does not "equal" anything, so "NULL = NULL" is false.
James Curran
Ok. That makes much more sense. Thanks for the help.
Xaisoft
I don't know the why of it. I just know it because I already have my idiot certification.
Corey Trager
lol, good to know I am not the only one left.
Xaisoft
As James alluded to, NULL means "unknown". You can't say that an unknown value is equal to a different unknown value, so it evaluates as false to ask if NULL = NULL
Tom H.
+1  A: 

You can cut out the If completely. Try this:

CREATE PROCEDURE [dbo].[GetRadioServiceCodes] 
@RadioServiceGroup nvarchar(1000) = NULL
AS
BEGIN              
    SELECT rsc.RadioService
    FROM sbi_l_radioservicecodes rsc
    WHERE 
        rsc.RadioServiceGroup = @RadioServiceGroup    
        OR @RadioServiceGroup IS NULL
END

Make sure you add any necessary parentheses to group these if the where clause gets more complicated.

Joel Coehoorn
+1  A: 

Use "IS NULL" instead of "= NULL"

Alternatively, to make "= NULL" work you can write "SET ANSI_NULLS OFF" before "CREATE PROCEDURE".

Jonas
A: 

Curious, is it just a syntax thing on why = does not work on nvarchar for NULL

Some people are saying that NULL = NULL evaluates to false. This is wrong. NULL = NULL evaluates to NULL.

The result (NULL) is not true, so the ELSE condition is run.

See also : ThreeValuedLogic on Wikipedia

Another affect you should be aware of - Concatenation: NULL + @SomeVar evaluates to NULL. This can wipe out a computed string unexpectedly.

David B