Avatar of LukePerrin01
LukePerrin01
 asked on

How do I only include an "and" statement in a where clause if a variable has a value in it?

How do I only include an "and" statement in a where clause if a variable has a value in it? I need to modify a real long stored procedure and I only want it to match an Id if that varibale has a value, I saw some examples of using a case statement but cant figure out how to get it working. If the variable string is empty then I want to execute the other stuff in the where clause otherwise I want to retrieve back a specific record to match the variable.

Thanks
Luke
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Imran Javed Zia

8/22/2022 - Mon
IanTh

Patrick Matthews

An example would be most helpful :)
Imran Javed Zia

Hi either you can go fo dymanic sql and add check to add and condition if required.

alternatively you can use Case stement in the where clase lisk


Select * from yourTable
Where Filed1 = @var1
And Field2 = (Case When IsNull(@var2, 0) > 0 Then @var2 Else Field2 End)

Thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
tausifsfarid

SELECT *
FROM ORDERS
WHERE (@VAR1 = 'Customers' and CustomerID = @VAR2) AND
      (@VAR1 = 'Employee' and EmployeeID = @VAR2)
Patrick Matthews

tausifsfarid,

I think perhaps you meant:

SELECT * 
FROM ORDERS
WHERE (@VAR1 = 'Customers' and CustomerID = @VAR2) OR
      (@VAR1 = 'Employee' and EmployeeID = @VAR2)

Open in new window


As you wrote it, it is impossible for both @VAR1 = 'Customers' and @VAR1 = 'Employee' to be true :)

Patrick
LukePerrin01

ASKER
Here is the example, I didnt paste as its so long but just go straight to the very end and you can see the AND i put in for the uniqueID, but if the variable is empty then i still want to return the other stuff?

I may put in an if-else and just do this below is its empty and have an else if its not empty to just get back the uniqueID, would that be the cleanest way? Im just wondering how I could achieve the same thing with a case?



 SELECT  TOP 1000
                        dbo.Workflows.WorkflowID,
                        dbo.WorkflowSteps.WorkflowStepID,
                        IssueWorkflows.WorkflowID AS IssueWorkflowID,
                        dbo.Manuscripts.ManuscriptID,
                        ISNULL(CAST(dbo.Manuscripts.cUniqueID AS VARCHAR(50)), Issues.cTitle) AS IssueDescription,
                        dbo.Workflows.dScheduledStart AS ScheduledStart,
                        dbo.Workflows.dActualStart AS ActualStart,
                        dbo.Workflows.dScheduledCompletion AS ScheduledCompletion,
                        dbo.Workflows.dActualCompletion AS ActualCompletion,
                        dbo.WorkflowSteps.WorkflowStepID AS CurrentStepID,
                        dbo.Workflows.WorkflowTypeID,
                        dbo.WorkflowTypes.cDescription AS WorkflowTypeDescription,
                        dbo.Workflows.JournalID,
                        dbo.Journals.cShortTitle AS JournalTitle,
                        ISNULL(dbo.Manuscripts.IssueID, dbo.Workflows.IssueID) AS IssueID,
                        ISNULL(IssueWorkflows.VolumeID, dbo.Workflows.VolumeID) AS VolumeID,
                        Issues.dScheduledOnline AS ScheduledOnline,
                        dbo.Workflows.nDurationRemaining AS DurationRemaining,
                        dbo.Workflows.nStepCount AS StepCount,
                        dbo.Workflows.dProjectedEnd AS ProjectedEnd,
                        IssueWorkflows.dProjectedEnd AS IssueProjectedEnd,  
                        CASE
                              WHEN Workflows.WorkflowTypeID = @WORKFLOW_TYPE_ISSUE
                              THEN NULL
                              ELSE
                                    CASE
                                          WHEN IssueWorkflows.dProjectedEnd IS NOT NULL
                                          THEN
                                                CASE
                                                      WHEN Workflows.dProjectedEnd <= ISNULL(dbo.fnGetTargetStepProjectedEndDate(IssueWorkflows.WorkflowID), IssueWorkflows.dProjectedEnd)
                                                      THEN dbo.GetWorkflowStepStatusTypeDescription(@STATUS_ONTARGET)
                                                      ELSE dbo.GetWorkflowStepStatusTypeDescription(@STATUS_OVERDUE)
                                                END
                                          ELSE NULL
                                    END
                        END AS StatusREIssue,
                        dbo.Manuscripts.cUniqueID AS ManuscriptUniqueID,
                        (
                              SELECT      TOP (1) dbo.Stakeholders.cDisplayName
                              FROM      dbo.JournalTeamMembers (NOLOCK)
                                          INNER JOIN dbo.Stakeholders (NOLOCK)
                                          ON dbo.JournalTeamMembers.StakeholderID = dbo.Stakeholders.StakeholderID
                              WHERE      dbo.JournalTeamMembers.JournalTeamID = dbo.OverridableJournalParameters.JournalTeamID
                              AND            dbo.JournalTeamMembers.JournalTeamRoleID = @ROLE_PRODUCTION_EDITOR
                        ) AS ProductionEditorName,
                        (
                              SELECT      TOP (1) dbo.Stakeholders.cDisplayName
                              FROM      dbo.JournalTeamMembers (NOLOCK)
                                          INNER JOIN dbo.Stakeholders (NOLOCK)
                                          ON JournalTeamMembers.StakeholderID = dbo.Stakeholders.StakeholderID
                              WHERE      JournalTeamMembers.JournalTeamID = dbo.OverridableJournalParameters.JournalTeamID
                              AND            JournalTeamMembers.JournalTeamRoleID = @ROLE_TEAM_LEADER
                        ) AS TeamLeaderName,
                        CASE
                              WHEN WorkflowSteps.nStatus = @STATUS_INPROGRESS
                              THEN
                                    CASE
                                          WHEN DATEDIFF(dd, dbo.fnWorkingDateAdd(WorkflowSteps.dActualStart, WorkflowSteps.nDuration), @date) > 0
                                          THEN dbo.GetWorkflowStepStatusTypeDescription(@STATUS_OVERDUE)
                                          ELSE dbo.GetWorkflowStepStatusTypeDescription(@STATUS_INPROGRESS)
                                    END
                              ELSE
                                    (
                                          SELECT      cDescription
                                          FROM      WorkflowStepStatusTypes (NOLOCK)
                                          WHERE      WorkflowStepStatusTypeID = WorkflowSteps.nStatus
                                    )
                        END AS CurrentStepStatus,
                        dbo.WorkflowSteps.RoleID AS CurrentStepRoleID,
                        dbo.JournalTeamRoles.cDescription AS CurrentStepRoleDescription,
                        dbo.Manuscripts.CopyrightTypeID,
                        dbo.CopyrightTypes.cDescription AS CopyRightTypeDescription,
                        dbo.WorkflowSteps.WorkflowStepTypeID AS CurrentStepTypeID,
                        dbo.WorkflowStepTypes.cDescription AS CurrentStepTypeDescription,
                        (
                              SELECT      dbo.Issues.nNumber
                              FROM      dbo.Issues (NOLOCK)
                              WHERE      dbo.Issues.IssueID = dbo.Manuscripts.IssueID
                        ) AS IssueNumber,
                        (
                              SELECT      dbo.Volumes.nNumber
                              FROM      dbo.Volumes (NOLOCK)
                              WHERE      dbo.Volumes.VolumeID = IssueWorkflows.VolumeID
                        ) AS VolumeNumber,
                        dbo.WorkflowSteps.nGroup AS [Group],
                        ISNULL(
                              ISNULL(
                                    ISNULL(
                                                (
                                                      SELECT      CAST(1 AS BIT)
                                                      WHERE      EXISTS
                                                                  (
                                                                        SELECT TOP 1 ManuscriptID
                                                                        FROM   ManuscriptNotes (NOLOCK)
                                                                        WHERE  ManuscriptNotes.ManuscriptID = Manuscripts.ManuscriptID
                                                                  )      
                                                ),
                                                (
                                                      SELECT      CAST(1 AS BIT)
                                                      WHERE      EXISTS
                                                                  (
                                                                        SELECT TOP 1 WorkflowStepID
                                                                        FROM   WorkflowStepNotes (NOLOCK)
                                                                        WHERE  WorkflowStepNotes.WorkflowStepID = WorkflowSteps.WorkflowStepID
                                                                  )      
                                                )
                                    ),
                                    (
                                          SELECT      CAST(1 AS BIT)
                                          WHERE      EXISTS
                                                      (
                                                            SELECT TOP 1 IssueID
                                                            FROM   IssueNotes (NOLOCK)
                                                            WHERE  IssueNotes.IssueID = Issues.IssueID
                                                      )            
                                    )
                              ),
                              CAST(0 AS BIT)
                        ) AS Notes,
                        ISNULL
                        (
                              (
                                    SELECT      CAST(1 AS BIT) AS Expr1
                                    WHERE      EXISTS
                                          (
                                                SELECT      TOP (1) AlertID
                                                FROM      dbo.Alerts (NOLOCK)
                                                WHERE      WorkflowStepID = dbo.WorkflowSteps.WorkflowStepID
                                          )
                              ),
                              CAST(0 AS BIT)
                        ) AS Alerts,
                        ISNULL
                        (
                              (
                                    SELECT      CAST(1 AS BIT) AS Expr1
                                    WHERE      EXISTS
                                          (
                                                SELECT      TOP (1) AlertID
                                                FROM      dbo.Alerts AS Alerts (NOLOCK)
                                                WHERE      WorkflowStepID = dbo.WorkflowSteps.WorkflowStepID
                                                AND            lAutoSend = 0
                                          )
                              ),
                              CAST(0 AS BIT)
                        ) AS ManualAlerts,
                        dbo.Workflows.nDuration AS Duration,
                        CASE
                              WHEN
                              (      
                                    (
                                          SELECT      dActualStart
                                          FROM      WorkflowSteps (NOLOCK)
                                          WHERE      WorkflowStepID = Workflows.FirstStepID
                                    ) IS NULL
                              )
                              THEN dbo.GetWorkflowStepStatusTypeDescription(@STATUS_NOTSTARTED)
                              ELSE
                                    CASE
                                          WHEN
                                          (
                                                (
                                                      SELECT      dActualEnd
                                                      FROM      WorkflowSteps (NOLOCK)
                                                      WHERE      WorkflowStepID = Workflows.LastStepID
                                                ) IS NOT NULL
                                          )
                                          THEN dbo.GetWorkflowStepStatusTypeDescription(@STATUS_COMPLETE)
                                          ELSE
                                                CASE
                                                      WHEN Workflows.dScheduledCompletion >= Workflows.dProjectedEnd
                                                      THEN dbo.GetWorkflowStepStatusTypeDescription(@STATUS_ONTARGET)
                                                      ELSE dbo.GetWorkflowStepStatusTypeDescription(@STATUS_OVERDUE)
                                                END
                                    END
                        END AS WorkflowStatusDescription,
                        dbo.Workflows.WorkflowTemplateID,
                        dbo.Manuscripts.lEmbargo AS Embargo
            FROM      dbo.Workflows (NOLOCK)
                        LEFT OUTER JOIN dbo.WorkflowTypes (NOLOCK)
                        ON dbo.Workflows.WorkflowTypeID = dbo.WorkflowTypes.WorkflowTypeID
                        LEFT OUTER JOIN dbo.Manuscripts (NOLOCK)
                        ON dbo.Manuscripts.WorkflowID = dbo.Workflows.WorkflowID
                        LEFT OUTER JOIN dbo.Issues AS Issues (NOLOCK)
                        ON dbo.Manuscripts.ManuscriptID IS NOT NULL
                                                AND dbo.Manuscripts.IssueID = Issues.IssueID OR dbo.Manuscripts.ManuscriptID IS NULL
                                                AND dbo.Workflows.IssueID = Issues.IssueID
                        LEFT OUTER JOIN dbo.Workflows (NOLOCK) AS IssueWorkflows
                        ON IssueWorkflows.WorkflowID = Issues.WorkflowID
                        LEFT OUTER JOIN dbo.Volumes AS Volumes (NOLOCK)
                        ON Volumes.VolumeID = Issues.VolumeID
                        LEFT OUTER JOIN dbo.CopyrightTypes (NOLOCK)
                        ON dbo.Manuscripts.CopyrightTypeID = dbo.CopyrightTypes.CopyrightTypeID
                        LEFT OUTER JOIN dbo.WorkflowSteps (NOLOCK)
                        ON dbo.WorkflowSteps.WorkflowID = dbo.Workflows.WorkflowID
                        LEFT OUTER JOIN dbo.JournalTeamRoles (NOLOCK)
                        ON dbo.WorkflowSteps.RoleID = dbo.JournalTeamRoles.JournalTeamRoleID
                        LEFT OUTER JOIN dbo.WorkflowStepTypes (NOLOCK)
                        ON dbo.WorkflowSteps.WorkflowStepTypeID = dbo.WorkflowStepTypes.WorkflowStepTypeID
                        LEFT OUTER JOIN dbo.Journals (NOLOCK)
                        ON dbo.Journals.JournalID = ISNULL(Volumes.JournalID, dbo.Workflows.JournalID)                         
                        LEFT OUTER JOIN dbo.OverridableJournalParameters (NOLOCK)
                        ON dbo.OverridableJournalParameters.OverridableJournalParameterID = dbo.Journals.OverridableJournalParameterID
                        LEFT OUTER JOIN dbo.CalendarYears (NOLOCK)
                        ON dbo.Manuscripts.CalendarYearID = dbo.CalendarYears.CalendarYearID
            WHERE   (Manuscripts.lDeleted IS NULL OR Manuscripts.lDeleted = 0)
                        AND (@JournalID IS NULL OR (@JournalID IS NOT NULL AND @JournalID = Workflows.JournalID))
                        AND (@WorkflowTypeID IS NULL OR (@WorkflowTypeID IS NOT NULL AND dbo.Workflows.WorkflowTypeID = @WorkflowTypeID))
                        AND (@WorkflowStatusTypeID IS NULL OR
                              (@WorkflowStatusTypeID IS NOT NULL AND
                                    (
                                          (@WorkflowStatusTypeID != @STATUS_INPROGRESS AND
                                          CASE
                                                WHEN
                                                (
                                                      (
                                                            SELECT      dActualStart
                                                            FROM      WorkflowSteps (NOLOCK)
                                                            WHERE      WorkflowStepID = Workflows.FirstStepID
                                                      ) IS NULL
                                                )
                                                THEN @STATUS_NOTSTARTED
                                                ELSE
                                                      CASE
                                                            WHEN
                                                            (
                                                                  (
                                                                        SELECT dActualEnd
                                                                        FROM   WorkflowSteps (NOLOCK)
                                                                        WHERE  WorkflowStepID = Workflows.LastStepID
                                                                  ) IS NOT NULL
                                                            )
                                                            THEN @STATUS_COMPLETE
                                                            ELSE
                                                                  CASE
                                                                        WHEN Workflows.dScheduledCompletion > Workflows.dProjectedEnd
                                                                        THEN @STATUS_ONTARGET
                                                                        ELSE @STATUS_OVERDUE
                                                                  END
                                                      END
                                          END = @WorkflowStatusTypeID
                                    )
                                    OR
                                    (@WorkflowStatusTypeID = @STATUS_INPROGRESS AND
                                    CASE
                                          WHEN
                                          (
                                                (
                                                      SELECT    dActualStart
                                                      FROM      WorkflowSteps (NOLOCK)
                                                      WHERE     WorkflowStepID = Workflows.FirstStepID
                                                      ) IS NULL
                                          )
                                          THEN @STATUS_NOTSTARTED
                                          ELSE
                                                CASE
                                                      WHEN
                                                      (
                                                            (
                                                                  SELECT  dActualEnd
                                                                  FROM    WorkflowSteps (NOLOCK)
                                                                  WHERE   WorkflowStepID = Workflows.LastStepID
                                                                  ) IS NOT NULL
                                                      )
                                                      THEN @STATUS_COMPLETE
                                                      ELSE
                                                            CASE
                                                                  WHEN Workflows.dScheduledCompletion > Workflows.dProjectedEnd
                                                                  THEN @STATUS_INPROGRESS
                                                                  ELSE @STATUS_INPROGRESS
                                                            END
                                                END
                                    END = @WorkflowStatusTypeID)
                                    )
                              )
                        )
                        AND (@TeamLeaderID IS NULL OR (@TeamLeaderID IS NOT NULL
                              AND (
                                          SELECT      TOP 1 StakeholderID
                                          FROM      JournalTeamMembers (NOLOCK)
                                          WHERE      JournalTeamID = OverridableJournalParameters.JournalTeamID
                                          AND            JournalTeamMembers.JournalTeamRoleID = @ROLE_TEAM_LEADER
                                    ) = @TeamLeaderID
                              )
                        )
                        AND (@ProductionEditorID IS NULL OR (@ProductionEditorID IS NOT NULL
                              AND (
                                          SELECT      TOP 1 StakeholderID
                                          FROM      JournalTeamMembers (NOLOCK)
                                          WHERE      JournalTeamID = OverridableJournalParameters.JournalTeamID
                                          AND            JournalTeamMembers.JournalTeamRoleID = @ROLE_PRODUCTION_EDITOR
                                    ) = @ProductionEditorID
                              )
                        )
                        AND (
                              (WorkflowSteps.dActualStart IS NOT NULL AND WorkflowSteps.dActualEnd IS NULL)
                              OR
                              (WorkflowSteps.WorkflowStepID = Workflows.CurrentStepID)
                              OR
                              (Workflows.CurrentStepID IS NULL AND WorkflowSteps.WorkflowStepID = Workflows.LastStepID)
                        )
                                                AND dbo.Manuscripts.cUniqueID = @UniqueID
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Imran Javed Zia

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
LukePerrin01

ASKER
Ah yea, thats perfect.. now it seems obvious! haha, im used to doing C# but so much sql, thanks!
Imran Javed Zia

Thanks Luke.