mo.notono.us

Wednesday, December 14, 2005

SQL: Bug in UDFs using COALESCE with SELECT and Multiple RETURNs?

I encountered a strange bug in some sql code I was refactoring this morning, when I replaced a number of IF .. ELSE statements with a simple COALESCE. The essence of the buggy code was as follows:

USE Northwind
GO

CREATE FUNCTION dbo.Foo (@EmployeeID int)
RETURNS nvarchar(20)
AS
BEGIN
 --Declare a variable that really shouldn't be used
 DECLARE @EmployeeName nvarchar(20)
 SET @EmployeeName = 'Not expected'
 RETURN COALESCE
  (
   (SELECT e.LastName FROM dbo.Employees e 
    WHERE e.EmployeeID = @EmployeeID), 
   '(Not Found)'
  )
 --The function SHOULD have exited by now...
 RETURN @EmployeeName
END

GO
--Both of these return unexpected results
SELECT dbo.Foo(1)          --Returns 'Not expected'
SELECT dbo.Foo(564654)  --Returns 'Not expected'
As can be seen from the code - I would have expected the first RETURN to exit the function. Instead what is actually returned is the contents of the @EmployeeName.

This appears to be a bug in how SQL handles the combination UDF, multiple RETURNs and a SELECT statement within a COALESCE.

(For the record, the following version of the function works fine:)

USE Northwind
GO

ALTER FUNCTION dbo.Foo (@EmployeeID int)
RETURNS nvarchar(20)
AS
BEGIN
 --Declare a variable that really shouldn't be used
 DECLARE @EmployeeName nvarchar(20)
 SET @EmployeeName = 'Not expected'
 RETURN COALESCE
  (
   (SELECT e.LastName FROM dbo.Employees e 
    WHERE e.EmployeeID = @EmployeeID), 
   '(Not Found)'
  )
 --The function SHOULD have exited by now...
 --RETURN @EmployeeName  <--SECOND RETURN COMMENTED OUT
END

GO
--These now return the expected results
SELECT dbo.Foo(1)           --Returns 'Davolio'
SELECT dbo.Foo(564654)   --Returns '(Not Found)'

Labels:

2 Comments:

Post a Comment

<< Home