We help IT Professionals succeed at work.

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

185 Views
Last Modified: 2012-05-29
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
An example would be most helpful :)
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
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
SELECT *
FROM ORDERS
WHERE (@VAR1 = 'Customers' and CustomerID = @VAR2) AND
      (@VAR1 = 'Employee' and EmployeeID = @VAR2)
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Author

Commented:
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
Consultant Software Engineer - .NET Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ah yea, thats perfect.. now it seems obvious! haha, im used to doing C# but so much sql, thanks!
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
Thanks Luke.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.