Link to home
Start Free TrialLog in
Avatar of Feyo
Feyo

asked on

Optional Date parameters

I'm trying to create a stored procedure that provides all records when the begin and end date parameters aren't passed, so I set their default to Null. However, that doesn't give me all records. What do I have to do to get all records when a date range is not specified. Here's an example of what I have tried:

        @ss varchar(50),
      @db varchar(50),
      @BeginDate varchar(50)=NULL,
      @EndDate varchar(50)=NULL


SET @Q1 = 'SELECT TBL1.cde_applicant_id, TBL2.txt_last, TBL2.txt_first,
            TBL2.dte_application_date, TBL1.cde_question, TBL1.cde_choice, TBL1.txt_response, TBL3.num_order, TBL4.cde_question_type FROM
            ' + @db + '.dbo.t_response as TBL1 INNER JOIN
            ' + @db + '.dbo.t_applicant as TBL2 ON TBL1.cde_applicant_id = TBL2.cde_applicant_id INNER JOIN
            ' + @db + '.dbo.t_choice as TBL3 ON TBL1.cde_choice = TBL3.cde_choice INNER JOIN
            ' + @db + '.dbo.t_question as TBL4 ON TBL1.cde_question = TBL4.cde_question
            WHERE TBL2.cde_selsys =' + @ss + ' and TBL2.dte_application_date BETWEEN ''' + @BeginDate + ''' and ''' + @EndDate + '''
            ORDER BY TBL1.cde_applicant_id, TBL1.cde_question'
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,


Set the @BeginDate to the min(TBL2.dte_application_date) if the variable is null and set @EndDate to max(TBL2.dte_application_date) if it is null. This way the code will always get the maximum range without hard coding any values.

Regards,

Lee
Avatar of Feyo
Feyo

ASKER

I went with the former solution because it was the first one I saw. Thanks for the help.