儘管定義了 RETURN VARCHAR(10)，但使用者定義的函式僅返回第一個字元？

每個人，

我無法弄清楚以下使用者定義的函式中缺少什麼：

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)

輸出僅包括“低”/“平均”/“高”返回值的第一個字元：

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

(1 row affected)

為什麼 MS SQL Server Management Studio 不考慮 VARCHAR(10)？

提前致謝！

Ps 請在下面找到我的輸出 - 我無法弄清楚為什麼它不能按預期工作？

ps2：函式只有一個參數，我好像沒有找到另一個同名的函式……下面可以找到函式相關的程式碼：

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

@@版本結果

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 版權所有 (C) 2019 Microsoft Corporation Express Edition (64-bit) o​​n Windows 10 Enterprise 10.0 (Build 18363:) 150

這是 2019 RTM 中 SQL Server 內聯函式的錯誤。

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

常量掃描中的表達式（CONVERT_IMPLICIT更改為CONVERT使其可執行）是

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) 

帶有文字的分支High有一個CONVERT(VARCHAR(1)截斷它的。

禁用該函式的內聯

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

• 或者安裝最新的 CU 來解決這個問題
• 或將函式中的所有字元串文字明確地轉換為VARCHAR(10)（這varchar(1)是由於RETURN ''
• 或重寫函式以減少程序性。由 inling 生成的表達式在原始程式碼中非常複雜，而下面的則更簡單（它能夠在編譯時不斷折疊dbo.ufn_GetSalaryLevel(100440)）'High'。

   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;

引用自：https://dba.stackexchange.com/questions/269287