Post a Comment On: mo.notono.us

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)'