Link to home
Start Free TrialLog in
Avatar of 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'
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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

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.


Avatar of Feyo


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