BUG: NULLIF or COALESCE with Multiple Expression Parameter Generates Access Violation (242454)

The information in this article applies to:

Microsoft SQL Server 6.5
Microsoft SQL Server 6.5 Service Pack 1 and later
Microsoft SQL Server 7.0
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.

Modification Type:	Minor	Last Reviewed:	7/15/2004
Keywords:	kbBug kbpending KB242454