Why doesn't setting a nvarchar variable to null return nothing in this stored proc?

Question

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

Answer 1

Try "IS NULL" instead of "= NULL"

Answer 2

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.

Answer 3

Use "IS NULL" instead of "= NULL"

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

Answer 4

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.