Stored procedure with parameters and dates

I have a database that has a column for 'filedon' which is a datetime column (SQL 2008)

I want to do a SP that will query the table and bring the records that match 'filedon' BEWTEEN two dates. This is simple. Here is the issue:

My search page has a 'from' and a 'to' fields:

1. If user leaves those fields blank then ALL results should be returned.  In my ASP I need to enter default dates for the parameters so I enter '01/01/1900' and '01/01/2050'  if user leaves it blank and those are the dates then it should return ALL records.

If the user DOES enter a value in the search page then the SP should return ONLY records that match that criteria (No nulls).

Here is a test version of the SP to try out:

ALTER PROCEDURE [dbo].[delete_report_byuser_01_results] 
@Firmid INT,
@filed_start DATE, 
@filed_end DATE

        a.Id ,
        a.CaseId ,
        z.RcptNo ,
        z.Filedon ,
        z.Approvedon ,
        s.description AS casestatus
FROM    Cases a
        LEFT JOIN Users AS f ON a.EmpId = f.UserId
        INNER JOIN dbo.admin_casestatus AS s ON s.idstatus = a.Archived
        LEFT JOIN Users AS d ON a.AlienId = d.UserId
        INNER JOIN FilingType AS k ON a.Processtype = k.FilingTypeId
        LEFT JOIN Employment AS L ON d.UserId = L.UserId
                                     AND L.CurEmp = 1
        LEFT JOIN FirmAddresses AS m ON a.FirmAddressId = m.Id
        INNER JOIN Processcatalog AS e ON e.ProcesscatalogID = a.Process
        INNER JOIN Atts AS J ON a.Id = J.CaseId
        INNER JOIN Users AS I ON J.UserId = I.UserId
        LEFT JOIN PreferenceType AS o ON o.PrefTypeid = a.PrefType
        LEFT JOIN Partiescont AS v ON d.UserId = v.PartcontId
                                      AND Relation = 'spouse'
        LEFT JOIN Users AS h ON v.UserId = h.UserId
        LEFT JOIN Activities_Misc AS z ON z.act_misc_id = a.Defaultrecpt
WHERE   a.FirmId = @Firmid
 AND ( z.Filedon BETWEEN @filed_start AND @filed_end ) 

Open in new window

I run it with the following paramters to simulate user entry:

EXECUTE [delete_report_byuser_01_results] 
	@Firmid = 2, 
    @filed_start = '01/01/2015',
    @filed_end = '01/01/2050'

Open in new window

It returns correct data. The problem is when user leaves it blank. Then it uses the default dates and takes out all the 'null' values.
The problem if I add 'null' to the WHERE clause like:

WHERE   a.FirmId = @Firmid
 AND ( z.Filedon BETWEEN @filed_start AND @filed_end ) OR z.Filedon IS NULL

Then it will return nulls also when I enter other dates. that are not the default dates entered by the ASP.

How can I modify the SP so that if the dates are different from '01/01/1900' AND /01/01/2050' then it should filter the results, BUT if those are the dates (which means user left them empty) then it should return ALL records.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You don't want to use between for dates ever.

ALTER PROCEDURE [dbo].[delete_report_byuser_01_results]
@Firmid INT,
@filed_start DATE = null,
@filed_end DATE =null

WHERE   a.FirmId = @Firmid
 and (@filed_start is null or z.FiledOn >= @filed_start) 
-- dateadd handles case of 12/31/2015 5:30 PM.  Give me everything before 1/1/2016 at 12:00 AM
and (@filed_end is null or z.filedon < dateadd(d,1,@filed_end))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Well, you then need to test OR @filed_start IS NULL OR @filed_end IS NULL instead of testing z.FiledOn.

Remember to put the whole condition in paranthesis, just one OR clause as part of the overall clause as a stray clause means the overall expression results in TRUE, no matter how many other partial conditions are FALSE.

So it should be

AND (( z.Filedon BETWEEN @filed_start AND @filed_end ) OR @filed_start IS NULL)

It should not be necessary to test, whether @filed_end is NULL as both veriables would become NULL, if you don't want to filter by these parameters.

Bye, Olaf.
AleksAuthor Commented:
What is the problem with 'between' as compared to <=  and =>  ?
Second. the ASP will pass '01/01/1900 and 01/01/2050' as default values when the fields are left blank, in which case the query should return ALL records.
The above does not take care of that. What do we do then ?  A

And if I pass null then it only returns those that are null and not all.

EXECUTE [delete_report_byuser_01_results]
      @Firmid = 2,
    @filed_start = null,
    @filed_end = null

So ... how do we take care of that scenario ?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Olaf DoschkeSoftware DeveloperCommented:
Do you understand OR?

If you pass in @filed_start = null and have a clause x OR @filed_start IS null, then the overall expression is TRUE and therefore does not filter any record, no matter if the z.Filedon dates are NULL or any value. The whole filter clause is put to TRUE and becomes inactive.

Therefore you should consider changing your ASP.NET to default to NULL instead of default date range, if the user doesn't enter a date range.

Bye, Olaf.
AleksAuthor Commented:
I can't change a whole system from ASP classic to .net
I can probably pass null, but when I ran a test of the SP using null I only got the records that were null and not ALL
AleksAuthor Commented:
I will try to pass null with ASP and see what I get.
AleksAuthor Commented:
Seems to have worked passing null value from the ASP. I will continue testing and open another ticket if needed. thank you.
Olaf DoschkeSoftware DeveloperCommented:
Sorry, forget about .NET vs classic, the main point is using NULL. It all about deciding whether you want to filter at all or not, and therefore allowing any data values you simplify things by passing in NULL instead of a default range and test the passed in values for NULL instead of the data.

A default range is acting in the same manner as the same date range entered by users, so of course that'll filter NULL out. And as you don't want that its easiest you have an opt out part of the whole logical expression, otherwise your opt out would be checking whether  @filed_start and  @filed_end are the default range.

What is simpler?

(... OR  @filed_start IS NULL)


(... OR (@filed_start= '01/01/1900' AND  @filed_end='01/01/2050') )

It's obviously a very stupid idea to pass in some default date range, if you don't want that to filter anything at all. For that to work, you would have to test these values to not have a filter effect.

Bye, Olaf.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.