Simple but interesting – In recent survey I found that many developers making this generic mistake. I have seen following code in periodic code review. (The code below is not actual code, it is simple sample code)
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
…
I decided to send following code to them. After running the following sample code it was clear that LEN of NULL values is not 0 (Zero) but it is NULL. Similarly, the result for DATALENGTH function is the same. DATALENGTH of NULL is NULL.
Sample Test Version:
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
PRINT 'LEN of NULL is 0'
ELSE
PRINT 'LEN of NULL is NULL'
Result Set:
LEN of NULL is NULL
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – LEN, BOL – DATALENGTH
