We help IT Professionals succeed at work.
Get Started

Stored procedure with parameters and dates

49 Views
Last Modified: 2016-01-11
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
Director of Information Technology
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 3 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE