Stored procedure with parameters and dates

Aleks
Aleks used Ask the Experts™
on
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

)
AS
SELECT 
        a.Id ,
        a.CaseId ,
        z.RcptNo ,
        z.Filedon ,
        z.Approvedon ,
        z.Validtodate,
        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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
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
)
--select


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

Olaf DoschkeSoftware Developer

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

Author

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 ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Olaf DoschkeSoftware Developer
Commented:
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.

Author

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
Brian CroweDatabase Administrator
Top Expert 2005
Commented:

Author

Commented:
I will try to pass null with ASP and see what I get.

Author

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 Developer

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

(... 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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial