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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
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
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbCodeSnippet kbpending KB275199
©2003 Microsoft Corporation. All rights reserved.