SP with Conditional Where clause where

  • CanuckBuck

    Hall of Fame

    Points: 3890

    I need to create a stored procedure with an optional where clause and I'm just not sure how to get it right.  Some help would be appreciated.

    Here's the guts of it;


    CREATE PROCEDURE [dbo].[AppTracker_Milestone_Dates_Browse_SP] @UserName VARCHAR(51), @State VARCHAR(11) AS
    SELECT
    <pkColumn> 
    <[Completeness Reviewer Name]> 
    <[Technical Reviewer Name]> 
    <[Decision Maker Name]> 
    <[Decision Date]> 
    <Other columns...>
    FROM (
    WHERE
       <Optional conditions>

    I want to search for the same user name in all three columns.  The user name could have a value, be null or it could be an empty string.  Additionally, the value supplied for Decision Date would either be NULL or NOT NULL

    Thanks in advance for any assistance.

  • ScottPletcher

    SSC Guru

    Points: 98434

    Something like this:


    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE PROCEDURE [dbo].[AppTracker_Milestone_Dates_Browse_SP]
        @UserName VARCHAR(51),
        @DecisionDate DATE,
        @State VARCHAR(11)
    AS
    SELECT
    <pkColumn>
    <[Completeness Reviewer Name]>
    <[Technical Reviewer Name]>
    <[Decision Maker Name]>
    <[Decision Date]>
    <Other columns...>
    FROM (
    WHERE
        (@UserName IN (<[Completeness Reviewer Name]>, <[Technical Reviewer Name]>, <[Decision Maker Name]>) AND
        (@DecisionDate IS NULL OR <[Decision Date]> = @DecisionDate)) <AND|OR>
       <Optional conditions>

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Luis Cazares

    SSC Guru

    Points: 183633

    You should read the Catch-all-query section from this article (and the whole article) to learn the best ways to do what you need.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • CanuckBuck

    Hall of Fame

    Points: 3890

    Thank you both for your responses.  Perhaps a little bit of back story will help explain what I'm trying to do.

    I have an MS Access database (2010) connected to SQL Server (2016).

    This is a better pseudo code representation of the view I'm working with
    SELECT

    [DB_Key]
    ,[Received Date]
    ,[Assignment Date]
    ,[Completeness Reviewer Name]
    ,[Assure Completeness End Date]
    ,[Technical Reviewer Name]
    ,[Technical Review End Date]
    ,[Decision Maker Name]
    ,[Decision Date]
    <Other columns...>

    FROM [Milestone_Dates_View]

    The default behavior I want for the main form, so as to limit the amount of data retrieved, is to return rows where the user's name is in one or more of the three [XXX Name] columns and the value of [Decision Date] is NULL.

    Users need to be able to change the filter to:

    • Show records where the Decision Date is not null
    • Show both null and not null Decision Dates
    • Find records belonging to someone else
    • Find records belonging to anyone
    • Find records belonging to no one (either Null OR empty string)
    Once the data is returned the users may further filter the data based on other values, including other dates.  This last requirement is key to the problem I'm  trying to solve.


    On my form I have;

    • a combo box listing all of the users PLUS a value representing All users and another value representing blank/null

    • a radio button group specifying;

    • Decided ([Decision Date] IS NOT NULL)
    • Undecided ([Decision Date] IS NULL)
    • All (no WHERE clause for [Decision Date])
    I'm presently using MS Access to apply a filter to the Rowsource (the underlying view) based on these parameters (I build up the WHERE clause in VBA based on the user's selections).  This works fine (it returns the correct rows) but... the values listed in the filter-by drop-down control of the dates in MS Access is not a distinct list of ALL of the dates in the column (which is what I want) but rather only the dates of rows where the value of the searched-for name is in the [XXX Name] column (because the searched-for name only needs to be in one of the three [XXX Name] columns in order to be included in the result set it is possible/likely that the other [XXX Name] columns may contain the names of other people).



    I'm not sure why this happens (I've searched for an explanation/solution but can not find one). My theory is that if I could filter the data in SQL Server rather than MS Access that MS Access would see an "unfiltered" data set and be able to list all of the date values in all of the date columns.  Hence my desire to figure out how to parameterize a where clause in a stored procedure.

    To complicate matters - for the case where the user needs to search for rows where the name is blank or null, I need to do an LTRIM(RTRIM([XXX Name])) to determine if it's blank (users could enter more than one space) as well using ISNULL([XXX Name], '') to substitute NULL values.  This is all fairly straightforward to do in VBA.

    All this to say...

    Scott's solution above works in the case where the user's name is supplied but not for the case where the name is NULL or blank.
    Neither does it address the;

    • NULL [Decision Date] OR
    • NOT NULL [Decision Date] OR
    • No WHERE clause for [Decision Date]

    cases.

    I am not committed to the solution being a stored procedure if there's another solution.  I'm also going to explore the idea of using a passthrough query from MS Access.

    If anyone has any other ideas I'm wide open.

    Again; thank you for your time to read and respond to this!

  • sgmunson

    SSC Guru

    Points: 110459

    Sounds a little like you have a cascading parameters issue, in that the available values for a given parameter may be dependent on the choices for a different parameter.   That's not going to be very easy in MS Access.   SSRS would give you that possibility, but only if the dependency is in a straight line, and that something early in the sequence of dependency CAN'T be dependent on something later in the sequence.   Mind you, you CAN do it in MS Access with VBA, but it's gonna be a pain and be a LOT of extra code to re-check all the other parameters a given parameter is dependent on.   The key for the resulting query is the technique Scott mentioned, e.g. checking a parameter value in a stored procedure for either NULL or a match to a column.  What may not be so easy in the stored procedure is determining how to get the parameters to interact.   Although, in the sproc, you don't EVER have to worry about a parameter changing values on you, as by the time you are executing the sproc, the parameter values are set in stone.  Thus the sproc only has to contend with a single set of parameter values, and has to just figure it out from their values.   Hope that helps...

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • CanuckBuck

    Hall of Fame

    Points: 3890

    Steve;
    Thanks for your response.  I'm not sure I completely understand what you're trying to say... 

    only if the dependency is in a straight line, and that something early in the sequence of dependency CAN'T be dependent on something later in the sequence.

    I'm also not sure what you mean by parameter interaction.

    To give you a better understanding of my current approach -  here's the code of the subroutine which currently builds the WHERE clause for the query based on the user's choices above.

    Private Sub SetFormFilter()
    Dim strFilterString As String

      'set the user value
      If cboUser = "<All Users>" Then 'We aren't using a name filter
       strFilterString = ""
      ElseIf cboUser = "<No Name>" Then 'This case lists applications where all name fields are NULL or empty strings
       strFilterString = "([Completeness Reviewer Name] IS NULL OR LTRIM(RTRIM([Completeness Reviewer Name])) = '')" _
       & " AND ([Technical Reviewer Name] IS NULL OR LTRIM(RTRIM([Technical Reviewer Name])) = '')" _
       & " AND ([Decision Maker Name] IS NULL OR LTRIM(RTRIM([Decision Maker Name])) = '')"
      Else
       strFilterString = "([Completeness Reviewer Name] = '" & cboUser _
       & "' OR [Technical Reviewer Name] = '" & cboUser _
       & "' OR [Decision Maker Name] = '" & cboUser & "')"
      End If

      'Set the state value
      If cboUser = "<All Users>" Then 'We aren't using a name filter so we don't need an AND operator in the filter string
       Select Case fraState.Value
        Case 2
          strFilterString = "[Decision Date] IS NULL"
        Case 3
          strFilterString = "[Decision Date] IS NOT NULL"
        Case Else
          strFilterString = ""
       End Select
      Else
       Select Case fraState.Value
        Case 2
          strFilterString = strFilterString & " AND [Decision Date] IS NULL"
        Case 3
          strFilterString = strFilterString & " AND [Decision Date] IS NOT NULL"
        Case Else
          strFilterString = strFilterString
       End Select
      End If
     
      If Len(strFilterString) = 0 Then
       Me.FilterOn = False
      Else
       Me.Filter = strFilterString
       Me.FilterOn = True
      End If
     
    End Sub

    Here's what the user sees;

    Here's the WHERE clause returned by the VBA code corresponding to the case above;

    ([Completeness Reviewer Name] = 'Scott Fallows' OR [Technical Reviewer Name] = 'Scott Fallows' OR [Decision Maker Name] = 'Scott Fallows') AND [Decision Date] IS NOT NULL

    When the user clicks the filter-by control (The down-pointing arrow in the header row) of any of the date columns, the only values listed for filtering are those from rows where the name is the same as the searched name - in this case the list of dates shows those for rows with Scott but not for Jennifer.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply