SP with Conditional Where clause where

Question

SP with Conditional Where clause where

CanuckBuck

Hall of Fame

Points: 3915
More actions
March 19, 2019 at 9:59 am

#383166

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.

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

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

ScottPletcher SSC Guru Points: 100946 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

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: 3915 More actions · Answer 3

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: 110639 More actions · Answer 4

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 5

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.