NULL value in Dynamic SQL becames Empty String

Recently,I encountered a strange (for me) situation.
Consider that you need to use some string parameter
in Dynamic SQL query using EXEC(@String) statement.
And the string parameter is NULL (by mistake).

USE AdventureWorks
GO
DECLARE @FirsName NVARCHAR(50)

EXEC(' SELECT ContactID
FROM Person.Contact
WHERE (FirstName = '''+@FirsName+''')')

What will happend is that the value of the @FirsName
will be empty string in execution runtime.
In SQL Profiler we will see the following executed:

SELECT ContactID
FROM Person.Contact
WHERE (FirstName = '')

Do you know why?
Anybody?

I got the answer from Ami Levin(Israel MVP):
This behavior explained by the settings of
"CONCAT_NULL_YIELDS_NULL" option.
http://msdn.microsoft.com/en-us/library/ms176056.aspx

Tweet