Depending on your ANSI_NULL setting in MSSQL (Microsoft SQL Server), you may get unexpected results. On one of our test databases, we have a UDF doing the wrong thing even though it was the same code as on another database where it was working correctly. It turns out that somehow the ANSI_NULL was turned OFF in the UDF that was not working correctly. I didn't really know exactly what ANSI_NULL does so I put together a little test SQL script to see what's going on:

SET ANSI_NULLS ON
DECLARE @testVar NVARCHAR(10)
SET @testVar = NULL
IF @testVar <> '' PRINT 'test 1: @testVar is not empty nor NULL'
SET @testVar = ''
IF @testVar <> '' PRINT 'test 2: @testVar is not empty nor NULL'
GO

SET ANSI_NULLS OFF
DECLARE @testVar nvarchar(10)
SET @testVar = NULL
IF @testVar <> '' PRINT 'test 3: @testVar is not empty nor NULL'
SET @testVar = ''
IF @testVar <> '' PRINT 'test 4: @testVar is not empty nor NULL'
GO

Here is the resulting output:

test 3: @testVar is not empty nor NULL

hmm, I don't want test 3 to output anything. It turns out that if ANSI_NULLS is OFF, a NULL value is not equal to '' whereas with the ANSI-92, NULL does equal ''.

So I guess the safe thing to do is either SET ANSI_NULL ON in the SPROC or UDF declaration or add a check for null in the IF conditions as follows:

SET ANSI_NULLS OFF
DECLARE @testVar nvarchar(10)
SET @testVar = NULL
IF @testVar <> '' AND @testVar IS NOT NULL PRINT 'test 3: @testVar is not empty nor NULL'
SET @testVar = ''
IF @testVar <> '' AND @testVar IS NOT NULL PRINT 'test 4: @testVar is not empty nor NULL'
GO