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

Avatar of undefined
Last Comment
Feyo

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lee

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
Feyo

ASKER
I went with the former solution because it was the first one I saw. Thanks for the help.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy