[RESOLVED] [SQL 2000] Conditional Conditions in SP

[RobDog888]

I have a sp that filters records in my datagrid (ASP.NET) but the optional parameters are throwing me off as the user can select any combination of date range only, date range and status, date range and scope, date range and status and scope, status and scope, status only, scope only.

How can I best create the dynamic where clause for this. This is what I have so far.

Thanks.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[usp_LoansFilter] AS 
    @From datetime = Null,
    @To datetime = Null,
    @Status nvarchar (15) = Null,
    @Scope nvarchar (15) = Null
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        [tblProspects].ProspectID,
        [tblProspects].FirstName + ', ' + [tblProspects].LastName AS [Prospect], 'For display purposes only
        [tblLoans].LoanID,
        [tblLoans].Status,
        [tblLoans].DateSubmitted,
        [tblLoans].PriceComplete,
        [tblLoans].FMA,
        [tblLoans].JFMA,
        [tblLoans].Purpose,
        [tblLoans].LoanAmount,
        [tblLoans].Created,
        [tblLoans].Last_Updated
    FROM
        tblLoans 
    INNER JOIN
        tblProspects 
        ON tblLoans.ProspectID = tblProspects.ProspectID 
    WHERE
        (([tblLoans].Created >= @From) AND ([tblLoans].Created <= @To))
            AND
        (([tblLoans].Last_Updated <= @From) AND ([tblLoans].Last_Updated <= @To))
            AND
        (([tblLoans].Status = @Status))
            AND
        (([tblLoans].PriceComplete = @Scope))
END
GO

[si_the_geek]

I would personally go for something like this:

WHERE
        ((@From Is Null) OR ([tblLoans].Created >= @From)) AND ((@To Is Null) OR ([tblLoans].Created <= @To)))
            AND ...

[RobDog888]

So this would equate to nothing if the @From is not passed? I always thought OR's concatenated values but I'm probably stuck on VB thinking and not SQL thinking.

Wouldnt it evaluate to ...

WHERE
AND AND ...

[si_the_geek]

If you look at just the first part:

((@From Is Null) OR ([tblLoans].Created >= @From))

If @From is Null then the first half will be True, so that will be:

((@True) OR ([tblLoans].Created >= @From))
=>True (as "True Or *" is True)

..so all rows will match with that condition

If @From is not Null then the first half will be false, so will be ignored (and the original clause will be used), eg:

((False) OR ([tblLoans].Created >= @From))
=>(([tblLoans].Created >= @From))  (as "False Or *" is *)

..so the original condition applies

[RobDog888]

Ok, that takes care of the Date range part but for the status part I have a "All" condition where it would equate to all status' so that should be treated as a Null using similar logic....

((@Status Is Null) OR ([tblLoans].Status = @Status))

But if "All" is passed then it wont be Null. Hmm, would it be good logic to eval the selection and only pass a status value if a specific one is selected vs. evaluating "all" in the sp?

[si_the_geek]

Do you mean the value will be something like "All"? If so, you can just compare against the value:

((@Status = 'All') OR ([tblLoans].Status = @Status))

..and set that as the default value for the parameter instead of Null.

If you mean that you will be sending multiple values, then something like an In clause would be better.. but I've had too much to drink to think of a method!

[brucevde]

For the Date Fields, instead of a Null default, I would give them a default value that would include all records in the range.

CREATE PROCEDURE [dbo].[usp_LoansFilter] AS 
	@From datetime = '01-Jan-1800',
	@To datetime = '31-Dec-2100 23:59:59',
	@Status nvarchar (15) = Null,
	@Scope nvarchar (15) = Null

Or if you use Null for a default, give them a value before executing the Select.

BEGIN
   SET NOCOUNT ON;

   If @From Is Null
       Set @From = '01-Jan-1800'

   If @To Is Null
       Set @To = '31-Dec-2100 23:59:59'

As for Status, same idea. If "ALL" is passed set the parameter to Null.

If @Status = "All"
Set @Status = Null

Where clause would not change.
((@Status Is Null) OR ([tblLoans].Status = @Status))

[RobDog888]

This is the whole clause then ...

WHERE
        (((@From Is Null) OR ([tblLoans].Created >= @From)) AND ((@To Is Null) OR ([tblLoans].Created <= @To)))
            AND
        (((@From Is Null) OR ([tblLoans].Last_Updated <= @From)) AND ((@To Is Null) OR ([tblLoans].Last_Updated <= @To)))
            AND
        ((@Status Is Null) OR ([tblLoans].Status = @Status))
            AND
        ((@Scope Is Null) OR ([tblLoans].PriceComplete = @Scope))

Basically...

Option one:
Date From - date value or Nullstring
Date To - date value or Nullstring

Option two:
Status - Nullstring, InProcess, Pending, Complete etc.

Option three:
Scope - Nullstring, Date Submitted, Date Created, Last Updated


So any combination of any of the three options is where I'm stuck.

[RobDog888]

Hmm, makes sense I think but could I or is it better to use a dynamic To date function?

CREATE PROCEDURE [dbo].[usp_LoansFilter] AS 
	@From datetime = '01-Jan-1800',
	@To datetime = GetDate(),
	@Status nvarchar (15) = Null,
	@Scope nvarchar (15) = Null

[si_the_geek]

As long as you send the appropriate parameters, the Where clause will work (no matter what combination you use), as each of your original clauses basically gets 'ignored' if the defaults haven't been changed.

I like Bruce's idea, but it means that checks need to be run on each record, whereas the idea I suggested would get short-circuted, and the entire clauses ignored (if the value is Null/default)

[RobDog888]

I think I'm going to leave the Date params with the Nulls as default but change the status and scope to his suggestion...

If @Status = "All"
Set @Status = Null

Then I can keep the where caluse as Si suggested.

Testing time

[RobDog888]

Not giving an error but not getting records back yet since the where clause is still kicking my butt.

The Scope parameter is for specifying which field the From and To date range encompus. If the Scope is CreatedDate then the where clause will be looking in that field. If the Scope is LastUpdated then @Scope looks at that.

So I am trying to get something like this to work.

WHERE
        IF (@Scope = 'Created')
            BEGIN
                (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Created, 101) >= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Created, 101) <= @To)))
                    AND
            END
        IF (@Scope = 'Last Updated') 
            BEGIN
                (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Last_Updated, 101) <= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Last_Updated, 101) <= @To)))
                    AND
            END
        IF (@Scope = 'Date Submitted')
            BEGIN
                (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].DateSubmitted, 101) >= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].DateSubmitted, 101) <= @To)))
                    AND
            END
        ((@Status Is Null) OR ([tblLoans].Status = @Status))

[RobDog888]

Got a little farther.

Valid syntax but not returning records yet. Not sure if its a date format issue or an eval issue.

WHERE
    (
        (@Scope = 'Created') 
            OR 
        (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Created, 101) >= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Created, 101) <= @To)))
    )
        AND
    (
        (@Scope = 'Last Updated') 
            OR
        (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Last_Updated, 101) <= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Last_Updated, 101) <= @To))) 
    )
        AND
    (
        (@Scope = 'Date Submitted')
            OR
        (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].DateSubmitted, 101) >= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].DateSubmitted, 101) <= @To))) 
    )
        AND
    ((@Status Is Null) OR ([tblLoans].Status = @Status))

[si_the_geek]

It looks to me as if you have a bit of a logic error.. you basically have:
"(scope=X OR (from....) AND (scope=Y ...", but should have:
"(scope=X AND (from....) OR (scope=Y ..."

eg:

WHERE
   [B]([/B]
    (
        (@Scope = 'Created') 
            AND
        (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Created, 101) >= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Created, 101) <= @To)))
    )
        OR
    (
        (@Scope = 'Last Updated') 
            AND
        (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Last_Updated, 101) <= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].Last_Updated, 101) <= @To))) 
    )
        OR
    (
        (@Scope = 'Date Submitted')
            AND
        (((@From Is Null) OR (CONVERT(nvarchar(10), [tblLoans].DateSubmitted, 101) >= @From)) AND ((@To Is Null) OR (CONVERT(nvarchar(10), [tblLoans].DateSubmitted, 101) <= @To))) 
    )
   [B])[/B]
        AND
    ((@Status Is Null) OR ([tblLoans].Status = @Status))

I assumed that the Status part should apply to all variations of Scope, so that still needs an And, with braces around the Scope part.

[RobDog888]

Statuis is a separate condition. I did some tracing and seens the @From and @To are in a different date format - Jan 1 2006 vs. the convert of 1/1/2006 but I wont be able to test until tonight. I have to leave as I am teaching a tranning class on some software.

[szlamany]

Rob - we have tried dozens of different methods to arrive at solutions to this and have found greate success with this:

For example - we have these parameters - which be be left blank or filled in:

	,@Yr int
	,@Bldg varchar(50)
	,@Sort_Order_Grade_Hrm_Team_Zip varchar(20)
	,@Grade_Range varchar(5)
	,@Homeroom varchar(5)
	,@Team varchar(2)

and we build a table variable with the primary keys we want to actually select:

Declare @IdList Table (StuId int, Bldg int, Grade varchar(2), Team varchar(2), HomeRoom varchar(5))

Insert into @IdList Select StuId,Bldg,Grade,Null,Null From ActiveStudent_V Where Yr=@Yr and Bldg=@Bldg

If IsNull(@Grade_Range,'')<>'' Delete From @IdList Where Grade<@LowGrade or Grade>@HighGrade or Grade is null

Update @IdList Set Homeroom=(Select Top 1 TV.Room From Class_T CL 
				Left Join Course_T CR on CR.Yr=CL.Yr and CR.Bldg=CL.Bldg and CR.Crs=CL.Crs
				Left Join Time_T TV on TV.Yr=CL.Yr and TV.Bldg=CL.Bldg and TV.Crs=CL.Crs and TV.Sectn=CL.Sectn
							and TV.TimeVec=CL.TimeVec
				WHERE (CL.StuId = IL.StuID 
					AND CL.Yr = @Yr AND CL.Bldg = @Bldg AND CR.CrsType='H')
					ORDER BY CL.AddDate Desc)
		From @IdList IL

If IsNull(@Homeroom,'')<>'' Delete From @IdList Where Homeroom<>@Homeroom or Homeroom is null

Update @IdList Set Team=RT.Team
		From @IdList IL 
		Left Join Room_T RT on RT.Yr=@Yr and RT.Bldg=@Bldg and RT.Room=IL.HomeRoom

If IsNull(@Team,'')<>'' Delete From @IdList Where Team<>@Team or Team is null

We build the table variable in a specific order that arrives at what we want in the fastest way possible.

We then come FROM the table variable:

	FROM @IdList IL
	INNER JOIN Student_T ST ON (ST.StuId = IL.StuId)

If you have a potential for lots of rows you want to use a TEMP TABLE instead of a table variable.

This has taken some of our reporting SPROCS that took 10's of minutes to run and dropped them to under a minute.

We also use the SI method often - the ((...is null) or (col matches)) concept. That's our typical method unless we run into speed or complexity issues.

What I like most about the method I showed here is that it's easy to upgrade and maintain - as it's done in stages. Sometimes we get asked for SORT or SELECT by ZIP for some output that never had it before. We simply add ZIP to the TABLE VARIABLE and we can now SELECT or ORDER on it.

Since we have dozens of common reportings SPROCS we typically just rip this stuff and copy/paste it around.

[RobDog888]

Seem to have it working correctly now (finally lol).

I used Si's updated where caluse


Thanks guys