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?
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
@@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 (thevarchar(1)is due to theRETURN '' -
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;