troubleshooting Question

Stored procedure with parameters and dates

Avatar of Aleks
AleksFlag for United States of America asked on
Microsoft SQL ServerWeb DevelopmentSQL
9 Comments3 Solutions52 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Kyle Abrahams
Director of Information Technology

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 3 Answers and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 3 Answers and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004