SQL2000 returns null where SQL7 returns empty string

[vbrunner]

I am testing a SQL7 to SQL2000 migration and have come across something that I can't find the source of.
I have a view in SQL7 that returns an empty string if a field in the source table is Null. The same view in SQL2000 returns Null in the same circumstance.

 

[Rosko]

Check the database option CONCAT_NULL_YIELDS_NULL and set it to off.


Maybe this can help


GL
Rosko

 

[vbrunner]

I have been advised not to set CONCAT_NULL_YIELDS_NULL to FALSE:

This is the workaround from Microsoft Technet - setting the CONCAT_NULL_YIELDS_NULL is NOT recommended.

PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile (Q294942)
The information in this article applies to:
Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server version 7.0
SYMPTOMS
If a stored procedure contains the statement SET CONCAT_NULL_YIELDS_NULL, and the statement changes the current setting of CONCAT_NULL_YIELDS_NULL, the procedure will be recompiled every time that it is executed. This may lead to performance problems.
CAUSE
Recompilation is required because changing the setting of CONCAT_NULL_YIELDS_NULL changes both the result and the way that commands are interpreted. In SQL Server 7.0 and SQL Server 2000, CONCAT_NULL_YIELDS_NULL is set to ON by default, in accordance with ANSI standards.

Setting CONCAT_NULL_YIELDS_NULL to OFF at the database level does not yield the desired result because ODBC and OLE DB set this property to ON when they connect. (Because this is specified by a bit in the connection packet, SQL Profiler does not show that this property is being set.)
WORKAROUND
Do not set CONCAT_NULL_YIELDS_NULL inside of the stored procedure. The recommended solution is to use the ISNULL function when concatenating strings.

For example, a query should be written as follows:
SELECT * FROM mytable WHERE name LIKE 'Xavier ' + ISNULL(@lastname,'') + '%'
rather than like this:
SELECT * FROM mytable WHERE name LIKE 'Xavier ' + @lastname + '%'
Although not recommended, an alternate solution would be to issue the SET CONCAT_NULL_YIELDS_NULL statement before executing the stored procedure.
MORE INFORMATION
This article addresses only one reason that stored procedures may be recompiled; for additional information, click the article number below to view the article in the Microsoft Knowledge Base:
Q243586 <

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586>

INF: Troubleshooting Stored Procedure Recompilation