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

Posted on

Question :

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?

enter image description here

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:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

enter image description here

@@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

Answer :

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 )
                                                                               ELSE ( 0 )
                                                                             END = ( 1 )
                                                                          THEN ( 1 )
                                                                        ELSE ( 0 )
                                                                      END
                                                                  END = ( 0 )
                                                           THEN 'High'
                                                         ELSE CONVERT(VARCHAR(4), 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 )
                                                                                         AND CASE
                                                                                               WHEN CASE
                                                                                                      WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                                        THEN ( 1 )
                                                                                                      ELSE ( 0 )
                                                                                                    END = ( 1 )
                                                                                                 THEN ( 1 )
                                                                                               ELSE ( 0 )
                                                                                             END = ( 0 )
                                                                                      THEN 'Average'
                                                                                    ELSE CONVERT(VARCHAR(7), CASE
                                                                                                               WHEN CASE
                                                                                                                      WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                                                        THEN ( 1 )
                                                                                                                      ELSE ( 0 )
                                                                                                                    END = ( 1 )
                                                                                                                 THEN 'Low'
                                                                                                               ELSE NULL
                                                                                                             END, 0)
                                                                                  END, 0)
                                                       END, 0)
                            END, 0) 

The branch with the literal High has a CONVERT(VARCHAR(1) that truncates it.

Disable inlining for that function

CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
WITH INLINE=OFF
AS
  • or install the latest CU to fix this

  • or cast all string literals in the function to VARCHAR(10) explictly (the varchar(1) is due to the RETURN ''

  • or rewrite the function to be less procedural. The expression generated by the inling is pretty complex in the original code and much simpler with the below (it is able to constant fold dbo.ufn_GetSalaryLevel(100440) to 'High' at compilation time).

    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;

Leave a Reply

Your email address will not be published. Required fields are marked *