ASP.NET MVC调用存储过程始终返回空值，求排查方案

开发ASP.NET MVC函数调用存储过程时遇到异常：存储过程在SSMS中手动执行可正常返回数据，但应用调用始终返回空值。此前同类调用逻辑从未出错，本次问题排查无果，相关代码如下：

应用调用代码 #

static object ArrayToString(List<string> array)
{
    if (array == null || array.Count == 0)
    {
        return DBNull.Value;
    }

    // Enclose each value in single quotes and join with commas
    string result = string.Join(",", array.Select(value => $"'{value}'"));

    return (object)result;
}

public List<MonthlySales> GetMonthlySales(FilterModel filter)
{
    var gender = ArrayToString(filter.gender);
    var region = ArrayToString(filter.region);
    var category = ArrayToString(filter.category);
    var residency = ArrayToString(filter.residency);
    var county = ArrayToString(filter.county);
    
    SqlParameter[] param = new SqlParameter[] 
        {
                new SqlParameter("@queryStartDate", filter.fromDate),
                new SqlParameter("@queryEndDate", filter.toDate),
                new SqlParameter("@procedure", "GetSalesByMonth"),
                new SqlParameter("@ageStart", filter.ageStart),
                new SqlParameter("@ageEnd", filter.ageEnd),
                new SqlParameter("@gender",ArrayToString(filter.gender)),
                new SqlParameter("@region", ArrayToString(filter.region)),
                new SqlParameter("@category",ArrayToString(filter.category)),
                new SqlParameter("@residency", ArrayToString(filter.residency)),
                new SqlParameter("@county",ArrayToString(filter.county))
        };

    var salesData = _brandt.Database.SqlQuery<MonthlySales>("LD.GetLicenseData_ListType @queryStartDate, @queryEndDate, @ageStart, @ageEnd, @gender, @county, @region, @procedure, @category, @residency", param).ToList();
    return salesData;
}

FilterModel定义 #

public class FilterModel
{
    public DateTime fromDate { get; set; }
    public DateTime toDate { get; set; }

    public List<string> region { get; set; }

    public string showInactive { get; set; }
    public string LicenseType { get; set; }

    public List<string> residency { get; set; }
    public List<string> gender { get; set; }
    public List<string> category { get; set; }

    public int ageStart { get; set; }
    public int ageEnd { get; set; }

    public List<string> county { get; set; }

    public FilterModel()
    {
          int year = DateTime.Now.Year;
          fromDate = new DateTime(year, 1, 1);
          toDate = DateTime.Today.Date;
          gender = null;
          category = null;
          ageStart = 0;
          ageEnd = 100;
          residency = null;
          LicenseType = null;
          county = null;
          region = null;
    }
}

存储过程代码 #

ALTER PROCEDURE [LD].[GetLicenseData_ListType]
    @queryStartDate DATE = NULL,
    @queryEndDate DATE = NULL,
    @ageStart INT = NULL,
    @ageEnd INT = NULL,
    @procedure VARCHAR(55) = NULL,
    @gender nvarchar(max) = NULL,
    @county nvarchar(max) = NULL,
    @region nvarchar(max) = NULL,
    @category nvarchar(max) = NULL,
    @residency nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Params NVARCHAR(MAX) = N'@queryStartDate DATE, @queryEndDate DATE, @ageStart INT, @ageEnd INT, @gender nvarchar(max), @region nvarchar(max), @procedure VARCHAR(55), @county nvarchar(max), @category nvarchar(max), @residency nvarchar(max)';
    DECLARE @SqlStr NVARCHAR(MAX);
    DECLARE @WhereCondition NVARCHAR(MAX);
    DECLARE @SelectStatement NVARCHAR(MAX);
    DECLARE @JoinStatement NVARCHAR(MAX);
    DECLARE @GroupByStatement NVARCHAR(MAX);
    DECLARE @OrderByStatement NVARCHAR(MAX);
    SET @GroupByStatement = ' ';
    
    -- Join Statement
    SET @JoinStatement = ' FROM [BRANDT_DBView].[LD].[VW_LicenseOrderInfo] AS ltoi ';
    
    -- Fixed as of 12/5
    IF (@procedure = 'VoidLicenseCount')
    BEGIN
        SET @SelectStatement = ' SELECT Region,
            COUNT(CASE WHEN isVoided = 1 THEN 1 END) AS voided_count,
            COUNT(CASE WHEN isVoided = 0 THEN 1 END) AS not_voided_count,
            ltoi.licenseTypeID';
        SET @GroupByStatement = ' GROUP BY Region, ltoi.licenseTypeID';
        SET @OrderByStatement = ' ORDER BY Region, ltoi.licenseTypeID ';
    END
    
    -- Fixed as of 12/5
    IF (@procedure = 'GetSalesByMonth')
    BEGIN
        SET @SelectStatement = ' SELECT COUNT(ltoi.orderID) AS amount,
            DATENAME(MONTH, ltoi.orderDate) AS [month] ';
        SET @GroupByStatement = ' GROUP BY DATENAME(Month, ltoi.orderDate) ';
        SET @OrderByStatement = ' ORDER BY DATENAME(MONTH, ltoi.orderDate)';
    END
    
    -- Fixed as of 12/5
    IF (@procedure = 'GetSalesByYear')
    BEGIN
        SET @SelectStatement = ' SELECT COUNT(ltoi.orderID) AS amount,
            YEAR(ltoi.orderDate) AS [Year] ';
        SET @GroupByStatement = ' GROUP BY YEAR(ltoi.orderDate) ';
        SET @OrderByStatement = ' ORDER BY YEAR(ltoi.orderDate);';
    END
    
    -- Fixed as of 12/5
    IF (@procedure = 'GetCategoryData')
    BEGIN
        SET @SelectStatement = ' SELECT COUNT(ltoi.orderID) AS [sales], lti.LicenseCategory AS category ';
        SET @GroupByStatement = ' GROUP BY lti.LicenseCategory ';
        SET @OrderByStatement = ' ';
    END
    
    IF (@procedure = 'GetCategoryData' AND @category IS NULL)
    BEGIN
        SET @JoinStatement += N' LEFT JOIN LD.VW_LicenseTypeInfo AS lti ON ltoi.licenseTypeId = lti.LicenseTypeID ';
    END
    SET @WhereCondition = ' WHERE ltoi.orderDate >= @queryStartDate  AND ltoi.orderDate <=  @queryEndDate ';
    
    IF (@ageStart IS NOT NULL OR @ageEnd IS NOT NULL)
        SET @WhereCondition += N' AND ltoi.Age BETWEEN @ageStart AND @ageEnd';
    
    IF (@gender IS NOT NULL)
        SET @WhereCondition += N' and ltoi.gender in (' + @gender + ')';
    
    IF (@region IS NOT NULL)
        SET @WhereCondition += N' AND ltoi.Region IN  (' + @region + ')';
    
    IF (@county IS NOT NULL)
        SET @WhereCondition += N' AND ltoi.CountyName IN (' + @county + ')';
    
    IF (@category IS NOT NULL)
    BEGIN
        SET @WhereCondition += N' AND lti.LicenseCategory IN (' + @category + ')';
        SET @JoinStatement += ' LEFT JOIN LD.VW_LicenseTypeInfo AS lti ON ltoi.licenseTypeId = lti.LicenseTypeID ';
    END

    -- Execute the dynamic SQL
    SET @SqlStr  = @SelectStatement + @JoinStatement + @WhereCondition + @GroupByStatement + @OrderByStatement;

    -- select @SqlStr as [month]

    EXEC sp_executesql @SqlStr, @Params, @queryStartDate, @queryEndDate, 
                       @ageStart, @ageEnd, @gender, @region, 
                       @procedure, @county, @category, @residency;

    --EXEC sp_executesql @SqlStr,
    --       N'@queryStartDate DATE, @queryEndDate DATE, @ageStart INT, @ageEnd INT, @gender nvarchar(max), @region nvarchar(max), @procedure VARCHAR(55), @county nvarchar(max), @category nvarchar(max), @residency nvarchar(max) ',
    --       @queryStartDate, @queryEndDate, @ageStart, @ageEnd, @gender, @region, @procedure, @county, @category, @residency;
END

1. 参数顺序不匹配（核心问题） #

存储过程的参数定义顺序与应用调用时的参数传递顺序完全不匹配：

• 存储过程中第5个参数是@procedure，但应用调用时将@gender放在了第5位，@procedure被推迟到第8位传递。
• 这导致存储过程接收到的@procedure参数值错误，根本没有触发GetSalesByMonth对应的查询逻辑，因此返回空结果。

修复方案：严格按照存储过程的参数定义顺序调整应用中的调用语句：

var salesData = _brandt.Database.SqlQuery<MonthlySales>(
    "LD.GetLicenseData_ListType @queryStartDate, @queryEndDate, @ageStart, @ageEnd, @procedure, @gender, @county, @region, @category, @residency", 
    param
).ToList();

2. 日期范围的时间边界问题 #

若数据库中orderDate是DateTime类型，存储过程中ltoi.orderDate <= @queryEndDate的条件会漏掉当天非0点的记录（比如orderDate为2024-05-20 15:30，@queryEndDate为2024-05-20时，会被转换为2024-05-20 00:00:00，导致该记录不满足条件）。

优化方案：调整存储过程中的日期条件：

SET @WhereCondition = ' WHERE ltoi.orderDate >= @queryStartDate  AND ltoi.orderDate < DATEADD(DAY, 1, @queryEndDate) ';

3. 实体类映射验证 #

确保MonthlySales类的属性名称与存储过程返回的列名完全一致，避免因映射失败导致空结果：

public class MonthlySales
{
    public int amount { get; set; }
    public string month { get; set; }
}

4. 动态SQL参数传递验证 #

存储过程中sp_executesql的参数列表顺序与@Params定义的顺序是匹配的，但需确保修复存储过程调用的参数顺序后，这里的传递也保持一致，避免动态SQL执行时参数错位。

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