Avatar of Aleks
AleksFlag for United States of America

asked on 

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

)
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.
Microsoft SQL ServerWeb DevelopmentSQL

Avatar of undefined
Last Comment
Olaf Doschke
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

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.
Avatar of Aleks
Aleks
Flag of United States of America image

ASKER

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 ?
SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Aleks
Aleks
Flag of United States of America image

ASKER

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
SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Aleks
Aleks
Flag of United States of America image

ASKER

I will try to pass null with ASP and see what I get.
Avatar of Aleks
Aleks
Flag of United States of America image

ASKER

Seems to have worked passing null value from the ASP. I will continue testing and open another ticket if needed. thank you.
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

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.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo