troubleshooting Question

Optional Date parameters

Avatar of Feyo
Feyo asked on
Microsoft SQL Server
3 Comments1 Solution289 ViewsLast Modified:
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'
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros