SQL Server存储过程返回值在C#中显示为NULL的问题排查

我有两个从SQL Server返回数据到C#的存储过程，逻辑几乎一致，区别在于其中一个多了翻译参数，用来从翻译表获取参考表的对应翻译内容。

基础存储过程 #

ALTER PROCEDURE [dbo].[BasicOutput]
@StartDate DATETIME2,
@EndDate DATETIME2,
@StartIndex INT,
@RowSize INT
AS
BEGIN
    DECLARE @AreaCode NVARCHAR(20) = 'TEST' 

    SELECT * FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY d.[Date] ASC) AS RowNum, ISNULL(la.[Name], 'Not Specified') AS LA, ISNULL(ag.AgeGroup5yr, 'Not Specified') AS AgeGroupId, d.[Year], d.QuarterNameShort AS [Quarter], d.FinancialYearName + ' ' + d.FinancialQuarterNameShort AS FinancialYearAndQuarter, t.TimeBucket, d.DayOfWeekUK
        FROM dbo.TestData fa
        INNER JOIN dbo.DimDate d
        ON fa.AttendanceDateId = d.DateKey
        INNER JOIN dbo.DimTime t
        ON fa.AttendanceTimeId = t.TimeKey
        LEFT JOIN REF_LocalAuthority la
        ON fa.LA = la.LowerTierLAcode
        LEFT JOIN REF_AgeGroup ag
        ON fa.AgeGroupId = ag.ID
        WHERE d.[Date] >= @StartDate AND d.[Date] <= @EndDate
        AND (
            la.CtyCode = @AreaCode
            OR sc.CTYcode = @AreaCode
        )
    )a
    WHERE RowNum > @StartIndex AND RowNum <= (@StartIndex + @RowSize)
END

带翻译的存储过程 #

ALTER PROCEDURE [dbo].[OutputWithTranslation]
@StartDate DATETIME2,
@EndDate DATETIME2,
@StartIndex INT,
@RowSize INT,
@TranslateLanguageCode NVARCHAR(10) = NULL
AS
BEGIN
    DECLARE @LanguageId INT = NULL
    DECLARE @NotSpecifiedText NVARCHAR(20) = 'Not Specified'
    IF(@TranslateLanguageCode IS NOT NULL)
    BEGIN
        SET @LanguageId = (SELECT Id FROM TranslationLanguage WHERE LanguageCode = @TranslateLanguageCode);
        SET @NotSpecifiedText = (SELECT TranslationText FROM REF_GenericResponse gr LEFT JOIN REF_GenericResponse_Translation grtran ON gr.Id = grtran.ResponseId WHERE gr.[Text] = @NotSpecifiedText AND grtran.LanguageId = @LanguageId)      
    END
    DECLARE @AreaCode NVARCHAR(20) = 'TEST' 

    SELECT * FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY d.[Date] ASC) AS RowNum, COALESCE(latran.TranslationText, la.[Name], @NotSpecifiedText) AS LA, ISNULL(ag.AgeGroup5yr, @NotSpecifiedText) AS AgeGroupId, d.[Year], d.QuarterNameShort AS [Quarter], d.FinancialYearName + ' ' + d.FinancialQuarterNameShort AS FinancialYearAndQuarter, t.TimeBucket, d.DayOfWeekUK
        FROM dbo.TestData fa
        INNER JOIN dbo.DimDate d
        ON fa.AttendanceDateId = d.DateKey
        INNER JOIN dbo.DimTime t
        ON fa.AttendanceTimeId = t.TimeKey
        LEFT JOIN REF_LocalAuthority la
        ON fa.LA = la.LowerTierLAcode

        LEFT JOIN REF_LocalAuthority_Translation latran
        ON la.ID = latran.LocalAuthorityId AND (@TranslateLanguageCode IS NOT NULL AND latran.LanguageId = @LanguageId)

        LEFT JOIN REF_AgeGroup ag
        ON fa.AgeGroupId = ag.ID

        WHERE d.[Date] >= @StartDate AND d.[Date] <= @EndDate
        AND (
        la.CtyCode = @AreaCode
        OR sc.CTYcode = @AreaCode
        )
    )a
    WHERE RowNum > @StartIndex AND RowNum <= (@StartIndex + @RowSize)
END

问题现象 #

在SQL Server中执行时，两个存储过程的输出都正常，NULL值会被正确替换为Not Specified或对应的翻译文本。但在C#代码中接收返回结果时，带翻译的存储过程里本该显示Not Specified的字段却变成了NULL。试过用嵌套ISNULL替换COALESCE，没用；但在基础存储过程里添加@NotSpecifiedText变量并使用COALESCE(NULL, la.[Name], @NotSpecifiedText)却能正常工作。

原因分析 #

核心问题是**@NotSpecifiedText变量可能被赋值为NULL**，导致最终的COALESCE/ISNULL兜底逻辑失效：

看带翻译的存储过程里的这段逻辑：

IF(@TranslateLanguageCode IS NOT NULL)
BEGIN
    SET @LanguageId = (SELECT Id FROM TranslationLanguage WHERE LanguageCode = @TranslateLanguageCode);
    SET @NotSpecifiedText = (SELECT TranslationText FROM REF_GenericResponse gr LEFT JOIN REF_GenericResponse_Translation grtran ON gr.Id = grtran.ResponseId WHERE gr.[Text] = @NotSpecifiedText AND grtran.LanguageId = @LanguageId)      
END

当@TranslateLanguageCode不为空，但查询REF_GenericResponse_Translation时找不到对应的翻译记录，@NotSpecifiedText就会被设为NULL。此时后续的COALESCE(latran.TranslationText, la.[Name], @NotSpecifiedText)会因为第三个参数是NULL，最终返回NULL。

而基础存储过程直接用字符串常量'Not Specified'，不存在被覆盖为NULL的情况；你在基础存储过程测试时，@NotSpecifiedText是初始值'Not Specified'，未被修改，所以正常工作。

另外注意：两个存储过程的WHERE子句里都用到了sc.CTYcode，但FROM子句中没有引入表sc，这会导致编译错误，建议检查是否遗漏了表连接。

解决方案 #

1. 给@NotSpecifiedText的赋值语句添加ISNULL兜底，确保就算找不到翻译也不会变成NULL，同时查询条件用原始字符串常量避免变量覆盖问题：

IF(@TranslateLanguageCode IS NOT NULL)
BEGIN
    SET @LanguageId = (SELECT Id FROM TranslationLanguage WHERE LanguageCode = @TranslateLanguageCode);
    -- 用ISNULL兜底，查询条件用原始常量'Not Specified'
    SET @NotSpecifiedText = ISNULL(
        (SELECT TranslationText FROM REF_GenericResponse gr 
         LEFT JOIN REF_GenericResponse_Translation grtran ON gr.Id = grtran.ResponseId 
         WHERE gr.[Text] = 'Not Specified' AND grtran.LanguageId = @LanguageId),
        'Not Specified'
    )      
END

2. 修复WHERE子句中未定义的sc表问题，补充对应的表连接或修正条件。

内容的提问来源于stack exchange，提问作者bowfinger