3

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

  • 1
    Instead of ELSE RETURN 'High'; you could just RETURN 'HIGH'and skip the last RETURN. Not that it matters for your problem. – Lennart Jun 16 at 18:14
  • Sorry for omitting this info initially - I have run your query: 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 <X64> (Build 18363: ) 150 – query_question Jun 16 at 19:42
10

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;
| improve this answer | |
  • 1
    By adding RETURN CAST('' AS VARCHAR(10)) the characters show up to 4 (the length of the string 'High'. By disabling inlining the issue is solved and even with VARCHAR(MAX) for testing with different long strings in the output everything works as intended. Thanks much for all contributions and sorry if this question spammed you. – query_question Jun 16 at 19:54
  • @query_question thanks for the update. sounds like you would need to explicitly cast every string literal to varchar(10) for that approach to work, which is a bit tedious. You should consider patching to latest CU anyway to get the latest bug fixes – Martin Smith Jun 16 at 20:19
  • 1
    I have just updated to the latest CU, as advised - the strings are returned with all their characters by applying the very initial code! current ver.: Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14 2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Express Edition – query_question Jun 16 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.