Microsoft KB Archive/275199

= BUG: UDF that Returns the Results of a CASE with a Correlated Subquery Returns Error Message 107 =

Article ID: 275199

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q275199



BUG #: 236439 (SHILOH)



SYMPTOMS
When you attempt to create a user defined function (UDF) that returns the result of a CASE statement that contains a correlated subquery, the following error message may occur:

Server: Msg 107, Level 16, State 2, Procedure fn_getnewkey, Line 4

The column prefix 'A' does not match with a table name or alias name used in the query.

The statement has been terminated.



WORKAROUND
Rewrite the UDF so that the UDF assigns the result of the CASE statement to a local variable, and then returns that variable.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
Here is a user defined function that returns the error message: CREATE FUNCTION getkey RETURNS int AS BEGIN RETURN CASE WHEN EXISTS (SELECT * FROM sysobjects WHERE id = 1) THEN (SELECT MIN(id) + 1 FROM sysobjects AS A              WHERE NOT EXISTS(SELECT * FROM sysobjects WHERE A.id = id + 1)) ELSE 1 END END GO Here is the modified user defined function, which works correctly: CREATE FUNCTION getkey RETURNS int AS BEGIN DECLARE @getkey INT SELECT @getkey = CASE WHEN EXISTS (SELECT * FROM sysobjects WHERE id = 1) THEN (SELECT MIN(id) + 1 FROM sysobjects AS A              WHERE NOT EXISTS(SELECT * FROM sysobjects WHERE id = A.id + 1)) ELSE 1 END RETURN @getkey END GO

Additional query words: Error Message 107

Keywords: kbbug kbcodesnippet kbpending KB275199

-

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

© Microsoft Corporation. All rights reserved.