CODEWITHSUNDEEP
sql-servert-sql
David Tunnell
David Tunnell

Reputation: 7532

WHERE clause running differently depending on parameter value

I am working on a large query:

DECLARE @userParam VARCHAR(100)
    ,@startDateParam DATETIME
    ,@endDateParam DATETIME
    ,@orgTeamPK VARCHAR(100)
    ,@search VARCHAR(100)
    ,@productId VARCHAR(100)
    ,@themeParam VARCHAR(100)

SET @userParam = 'David Tunnell (tunnelld)'
SET @startDateParam = '2014-01-27'
SET @endDateParam = '2014-02-02'
SET @orgTeamPK = '%'
SET @search = '%'
SET @productId = '%'
SET @themeParam = '%'

SELECT '3' AS RowType
        ,DTH.EnteredBy AS Person
        ,COALESCE(PDT.[Name], APP.AppName) AS Project
        ,(
            CASE 
                WHEN (
                        STY.KanBanProductId IS NOT NULL
                        AND STY.SprintId IS NULL
                        )
                    THEN 'KanBan'
                WHEN (
                        STY.KanBanProductId IS NULL
                        AND STY.SprintId IS NOT NULL
                        )
                    THEN 'Sprint'
                ELSE SCY.Catagory
                END
            ) AS ProjectType
        ,COALESCE(STY.[Number], NSS.IncidentNumber) AS StoryNumber
        ,COALESCE(STY.Title, NSS.[Description]) AS StoryTitle
        ,CONVERT(VARCHAR(20), STY.Effort) AS Effort
        ,COALESCE(TSK.[Name], '') AS Task
        ,CONVERT(VARCHAR(20), TSK.OriginalEstimateHours) AS OriginalEstimateHours
        ,SCY.Catagory AS Category
        ,NSS.IncidentNumber AS IncidentNumber
        ,APP.AppName AS ApplicationName
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 2
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS MondayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 3
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS TuesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 4
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS WednesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 5
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS ThursdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 6
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS FridayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 7
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SaturdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 1
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SundayHours
        ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
        ,CAST(SUM(CASE 
                    WHEN DTH.Hours > 0
                        THEN DTH.[UserDifference]
                    END) AS VARCHAR(20)) AS DifferentUsers
        ,CAST(SUM(CASE 
                    WHEN DTH.Hours > 0
                        THEN DTH.DoubleBookedFlag
                    END) AS VARCHAR(20)) AS DoubleBookedFlag
        ,DTH.PointPerson AS PointPerson
    FROM DailyTaskHours DTH
    LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
    LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
    LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
    LEFT JOIN SupportCatagory SCY ON NSS.CatagoryId = SCY.PK_SupportCatagory
    LEFT JOIN [Application] APP ON NSS.ApplicationId = APP.PK_Application
    LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
    LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
    LEFT JOIN [User] USR ON DTH.EnteredBy = USR.DisplayName
    LEFT JOIN [StoryProductTheme] SPM ON STY.PK_Story = SPM.StoryId
    LEFT JOIN [ProductTheme] PTM ON SPM.ProductThemeId = PTM.PK_ProductTheme
    WHERE DTH.EnteredBy LIKE @userParam
        AND ActivityDate >= @startDateParam
        AND ActivityDate <= @endDateParam
        AND 1 = CASE ISNUMERIC(@productId)
            WHEN 0
                THEN CASE 
                        WHEN DTH.TaskId IS NULL
                            OR PDT.PK_Product LIKE @productId
                            THEN 1
                        END
            WHEN 1
                THEN CASE 
                        WHEN DTH.TaskId IS NOT NULL
                            AND PDT.PK_Product = @productId
                            THEN 1
                        END
            END
        AND (
            (
                @orgTeamPK = '%'
                AND (
                    USR.[OrganizationalTeamId] LIKE @orgTeamPK
                    OR USR.[OrganizationalTeamId] IS NULL
                    )
                )
            OR (
                @orgTeamPK <> '%'
                AND (USR.[OrganizationalTeamId] LIKE @orgTeamPK)
                )
            AND (
                (
                    STY.Number LIKE @search
                    OR STY.Number IS NULL
                    )
                OR (
                    STY.Title LIKE @search
                    OR STY.Title IS NULL
                    )
                OR (
                    TSK.NAME LIKE @search
                    OR TSK.NAME IS NULL
                    )
                )
            )
    GROUP BY DTH.EnteredBy
        ,PDT.[Name]
        ,SPT.[Name]
        ,SPT.[Description]
        ,STY.[Number]
        ,STY.Title
        ,TSK.[Name]
        ,SCY.Catagory
        ,NSS.IncidentNumber
        ,APP.AppName
        ,STY.KanBanProductId
        ,STY.SprintId
        ,NSS.[Description]
        ,TSK.OriginalEstimateHours
        ,STY.Effort
        ,DTH.PointPerson
    HAVING SUM(DTH.[Hours]) > 0

Whan I am trying to do is add the ability to filter in a specific way. Here is the code I am adding to the WHERE clause:

    AND (
        ISNUMERIC(@themeParam) = 0
        AND PTM.ThemeId LIKE @themeParam
        )
    OR (
        ISNUMERIC(@themeParam) = 1
        AND @themeParam = 6
        AND STY.KanBanProductId IS NULL
        AND STY.SprintId IS NOT NULL
        )
    OR (
        ISNUMERIC(@themeParam) = 1
        AND @themeParam <> 6
        AND PTM.ThemeId = @themeParam
        )

This is my goal with the code:

If @themeParam = '%', I want the first portion to run because ISNUMERIC(@themeParam) should evaluate to zero.

If @themeParam = 6, I want the second portion to run and return all values STY.KanBanProductId IS NULL AND STY.SprintId IS NOT NULL.

If @themeParam <> 6, I want the first to run, returning only where PTM.ThemeId = @themeParam.

I have two problems. Fist of all I am getting the error:

Conversion failed when converting the varchar value '%' to data type int.

I would think that if ISNUMERIC(@themeParam) does not evaluate to 1, the rest of the code would not run and it wouldn't try to compare '%' to 6.

My second problem is that when I do input a number for @themeParam the query runs but seems to ignore all of the other portions of my where clause and I don't understand why.

What am I doing wrong and how do I fix it? Thanks in advance.

Upvotes: 0

Views: 90

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Do not add criteria for the outer joined tables to the WHERE clause but to the ON clause or consider NULLS. Here is why: Once there is no matching record in an outer joined table like PTM a pseudo record is being created with all values set to NULL. When asking for PTM.ThemeId LIKE @themeParam OR ... you may mistakenly eliminate those records again.

Upvotes: 1

Related Questions