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
No comments:
Post a Comment