Why is setting the nvarchar variable not null in this saved proc?

Question

If @RadioServiceGroup is set to NULL, I want to return all entries from the sbi_l_radioservicecodes table, which contains about 120 entries. However, when I perform the following procedure and set @RadioServiceGroup to NULL, it does not return any records. This is where proc is stored:

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

Curiously, this is just the syntax for why = does not work on nvarchar for NULL

Some say that NULL = NULL evaluates to false. This is not true. NULL = NULL evaluates to NULL.

The result (NULL) is incorrect, therefore the ELSE condition is satisfied.

See also: ThreeValuedLogic on Wikipedia

Another influence you should be aware of is Concatenation: NULL + @SomeVar evaluates to NULL. This may unexpectedly destroy the calculated string.

Answer 3

You can completely cut If . Try the following:

 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 the necessary parentheses to group them if the where clause becomes more complex.

Answer 4

Use "IS NULL" instead of "= NULL"

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