How to inject condition dynamically in sql query based on condition?-CodePudding

I have a stored procedure that currently returns calculates statistics for employee to show on dashboard to track employee sale performance for individual employee.

Now, I am modifying that stored procedure to return the statistics to see the sales performance per department.

 @EmployeeId int
 @DepartmentId int, 

SET @NewLeadForSlaes = (SELECT SUM(SaleStats) 
                        FROM SaleSummary AS SS (nolock) 
                        INNER JOIN CustomerSale AS CS ON SS.SaleId = CS.SaleId
                        WHERE CS.CityId = @CityId 
                          AND CS.EmployeeId = @EmployeeId 
                          AND SS.Type = 'Lead' 
                          AND SS.Name = 'Sale' 
                          AND (DATEFROMPARTS(SS.SaleYear, SS.SaleMonth, 1) >= @FromDate 
                          AND DATEFROMPARTS(SS.SaleYear, SS.SaleMonth, 1) < @ToDate));

So when it will for EmployeeId, DepartmentId will be null and vice versa.

This is the condition for employee:

 CS.EmployeeId = @EmployeeId

This is the condition for department:

 CS.DepartmentId = @DepartmentId

How do I inject the condition dynamically but still keeps 1 query?

CodePudding user response：

Give this a Try, Put an ISNULL as I have done below. Since You have already mentioned in your question that the @EmployeeId will be NULL for Department and Vice versa, a Simple ISNULL or a COALESCE should do the job for you

DECLARE 
    @EmployeeId int,
    @DepartmentId int

SET @NewLeadForSlaes = (
                        SELECT 
                            SUM(SaleStats) 
                            FROM SaleSummary AS SS (nolock) 
                                INNER JOIN CustomerSale AS CS 
                                    ON SS.SaleId = CS.SaleId
                                WHERE CS.CityId = @CityId 
                                    AND CS.EmployeeId = @EmployeeId 
                                    AND SS.Type = 'Lead' 
                                    AND SS.Name = 'Sale' 
                                    AND DATEFROMPARTS(SS.SaleYear, SS.SaleMonth, 1) >= @FromDate 
                                    AND DATEFROMPARTS(SS.SaleYear, SS.SaleMonth, 1) < @ToDate
                                    AND CS.EmployeeId = ISNULL(@EmployeeId,CS.EmployeeId) -- Pass @EmployeeId as NULL for DepartmentId
                                    AND CS.DepartmentId = ISNULL(@DepartmentId,CS.DepartmentId) -- Pass @DepartmentId as NULL for Employee
                        );

CodePudding user response：

This is some what of a stab in the dark, if I am honest, but what I think you want is an OR which checks that value matches the value of the column while the other parameter is NULL. I also make some changes to the WHERE, such as making it SARGable (though really you should be adding a date column). I remove the NOLOCK, as I doubt you need it too.

DECLARE @EmployeeId int, --oddly the comma was missing here, but
        @DepartmentId int;--, --there was a trailing comma here.

SELECT @NewLeadForSlaes = SUM(SaleStats) --Should this not be @NewLeadForSales? You're also missing the DECLARE
FROM dbo.SaleSummary AS SS --(NOLOCK) --Is there a good reason you're using the NOLOCk hint?
    INNER JOIN CustomerSale AS CS ON SS.SaleId = CS.SaleId
WHERE CS.CityId = @CityId
  AND ((CS.EmployeeId = @EmployeeId AND @DepartmentId IS NULL)
   OR  (CS.DepartmentId = @DepartmentId AND @EmployeeId IS NULL))
  AND SS.Type ='Lead'
  AND SS.Name ='Sale' 
--This is SARGable but it's messy. Add a date (and time?) column to your table
  AND ((SS.SaleYear = DATEPART(YEAR, @FromDate) AND SS.SaleMonth >= DATEPART(MONTH, @FromDate))
   OR  (SS.SaleYear > DATEPART(YEAR, @FromDate) AND SS.SaleYear < DATEPART(YEAR, @ToDate))
   OR  (SS.SaleYear = DATEPART(YEAR, @ToDate) AND SS.SaleMonth <= DATEPART(MONTH, @ToDate)))
OPTION (RECOMPILE);