I’m currently working on a function that calls a stored procedure that for some reason will always return empty for me. This stored procedure works when I run it manually inside SMSS, but for some reason will not work for me when I use it inside of my application. This is how I have always called stored procedures inside of my ASP.NET MVC applications and have never had a problem but now for some reason it will not work.

Here is how I call the stored procedure inside of my app

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;
}

Here is a look at the filter model I pass

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;
    }
}

And then finally here is my stored procedure:

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

Here is a screenshot kind of displaying my problem I copy and pasted all the value while debugging and pasted them inside SSMS as you can see I get a list of records inside SMSS but a return of 0 inside of my App.

enter image description here

I have tried everything I can think of and still can’t see what could be causing this problem

8

The parameter list order in the SQL passed into _brandt.Database.SqlQuery() call is different from your defined stored procedure parameter order.

var salesData = _brandt.Database.SqlQuery<MonthlySales>(
    "LD.GetLicenseData_ListType ..., @gender, @county, @region, @procedure, ...",
    param).ToList();
ALTER PROCEDURE [LD].[GetLicenseData_ListType]
    ...,
    @procedure VARCHAR(55) = NULL,
    @gender nvarchar(max) = NULL,
    @county nvarchar(max) = NULL,
    @region nvarchar(max) = NULL,
    ...
AS

I expect your passed-in null @gender parameter is being received as @procedure. As a result, no @procedure = 'xxx' code branch is being executed and @SelectStatement remains null.

Moving the @procedure parameter ahead of @gender in the list should fix your problem.

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

It might also make sense to move @procedure to the from of the list in both places, ahead of all of the filter parameters. I would also suggest reviewing and reordering all parameter related code so that the parameters are in the same order everywhere, even when it does not matter.