Microsoft KB Archive/294942

= PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile =

Article ID: 294942

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q294942



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

Keywords: kbprb KB294942

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.