Microsoft KB Archive/242454

= BUG: NULLIF or COALESCE with Multiple Expression Parameter Generates Access Violation =

Article ID: 242454

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 6.5 Service Pack 1
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 1
 * Microsoft SQL Server 7.0 Service Pack 2

-



This article was previously published under Q242454



BUG #: 18841 (SQLBUG_65)

BUG #: 56518 (SQLBUG_70)



SYMPTOMS
When you execute either of the following two statements, against the PUBS database, they may generate an Access Violation: SELECT coalesce((SELECT * FROM authors), 0)

-or-

SELECT nullif((SELECT * FROM authors), 0)



CAUSE
The problem is caused by the multiple expressions returned by the innermost SELECT statement causing either the COALESCE or NULLIF functions to fail. In fact, when executed on SQL Server 7.0, an error message similar to the following may occur:

Server: Msg 116, Level 16, State 1, Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server]

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.



WORKAROUND
Avoid using the COALESCE and NULLIF functions with multiple expressions as parameters.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5 and 7.0.

Additional query words: nullif coalesce T-SQL

Keywords: kbbug kbpending KB242454

-

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

© Microsoft Corporation. All rights reserved.