User-defined function returns first character only although RETURN VARCHAR(10) is defined?

Problem

Everyone,

I cannot figure out what is missing in the below user-defined function:

CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
AS
BEGIN 
    IF (@Salary < 30000) RETURN 'Low';
    ELSE IF (@Salary <= 50000) RETURN 'Average';
    ELSE RETURN 'High';
    RETURN '';
END;

SELECT dbo.ufn_GetSalaryLevel(100440)

The output is including only the first character of the 'Low'/'Average'/'High' return values:

-----------------------------------------------------------------------
H

(1 row affected)

Why is MS SQL Server Management Studio not taking the VARCHAR(10) into account?

Thanks in advance!

P.s. Please find my output below - I cannot figure out why it does not work as expected?

P.s.2: the function has a single parameter, I seem not to find another function with the same name... Below you can find the code related to the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
CREATE FUNCTION  
(
    -- Add the parameters for the function here
     
)
RETURNS 
AS
BEGIN
    -- Declare the return variable here
    DECLARE  

    -- Add the T-SQL statements to compute the return value here
    SELECT  = 

    -- Return the result of the function
    RETURN 

END
GO

@@Version results

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019
13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition
(64-bit) on Windows 10 Enterprise 10.0 (Build 18363: ) 150

Solution

This is a bug with SQL Server inline functions in 2019 RTM.

DECLARE @Salary MONEY = 100440;
SELECT dbo.ufn_GetSalaryLevel(@Salary)

The expression in the constant scan (with CONVERT_IMPLICIT changed to CONVERT to make it runnable) is

```
SELECT CONVERT(VARCHAR(10), CASE
WHEN CASE
WHEN CASE
WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 0 )
AND CASE
WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 0 )
THEN ( 1 )
ELSE
CASE
WHEN CASE
WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 0 )
AND CASE
WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 1 )
THEN ( 1 )
ELSE
CASE
WHEN CASE
WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 1 )
THEN ( 1 )
ELSE ( 0 )
END
END
END = ( 0 )
THEN ''
ELSE CONVERT(VARCHAR(1), CASE
WHEN CASE
WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 0 )
AND CASE
WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 0 )
AND CASE
WHEN CASE
WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 0 )
AND CASE
WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
THEN ( 1 )
ELSE ( 0 )
END = ( 1 )
THEN ( 1 )
ELSE
CASE
WHEN CASE
WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
THEN ( 1 )

Code Snippets

DECLARE @Salary MONEY = 100440;
SELECT dbo.ufn_GetSalaryLevel(@Salary)

SELECT CONVERT(VARCHAR(10), CASE
                              WHEN CASE
                                     WHEN CASE
                                            WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                              THEN ( 1 )
                                            ELSE ( 0 )
                                          END = ( 0 )
                                          AND CASE
                                                WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                  THEN ( 1 )
                                                ELSE ( 0 )
                                              END = ( 0 )
                                       THEN ( 1 )
                                     ELSE
                                       CASE
                                         WHEN CASE
                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                  THEN ( 1 )
                                                ELSE ( 0 )
                                              END = ( 0 )
                                              AND CASE
                                                    WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                      THEN ( 1 )
                                                    ELSE ( 0 )
                                                  END = ( 1 )
                                           THEN ( 1 )
                                         ELSE
                                           CASE
                                             WHEN CASE
                                                    WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                      THEN ( 1 )
                                                    ELSE ( 0 )
                                                  END = ( 1 )
                                               THEN ( 1 )
                                             ELSE ( 0 )
                                           END
                                       END
                                   END = ( 0 )
                                THEN ''
                              ELSE CONVERT(VARCHAR(1), CASE
                                                         WHEN CASE
                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                  THEN ( 1 )
                                                                ELSE ( 0 )
                                                              END = ( 0 )
                                                              AND CASE
                                                                    WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )

CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
WITH INLINE=OFF
AS

CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
    RETURNS VARCHAR(10) AS
    BEGIN
    RETURN CASE
             WHEN @Salary < 30000
               THEN 'Low'
             WHEN @Salary <= 50000
               THEN 'Average'
             ELSE 'High'
           END;
    
    END;

Context

StackExchange Database Administrators Q#269287, answer score: 11

Revisions (0)

No revisions yet.