PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile (294942)

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:

243586 INF: Troubleshooting Stored Procedure Recompilation