DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 294942 - Last Review: October 16, 2003 - Revision: 3.2

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  (http://support.microsoft.com/kb/243586/EN-US/ ) INF: Troubleshooting Stored Procedure Recompilation

APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB294942
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support