1

I have a procedure that takes, among other things, start and end date parameters. These two parameters are optional inputs and default to NULL. What I would like to do is modify one of the current SELECT statements so that if the start and end dates are supplied it will return only the results between those two dates. I considered just throwing a CASE WHEN... in the WHERE statement but that that is messy and does not seem right. Is that the appropriate way to deal with this or is there something more efficient?

Existing SELECT clause:

SELECT  do.OrdrNmbr AS 'Order Number',
        mb.BOLNmbr AS 'BOL Number',
        do.DlvryDt  AS 'Deliver Date',
        mh.Text3 AS 'Truck',
        do.DlvrdQntty AS 'Gallons Delivered',
        ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
    ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
    ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
    ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
   ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber;

and the start/end dates would run against do.DlvryDt.

P.S. I know the table/column names are horrid but this is a 3rd party application the query is running against.

Improve this question
7
  • 3
    (1) Please be careful with AS 'alias' syntax - this is deprecated (see sqlblog.org/2012/01/23/…). (2) Be careful declaring types like VARCHAR without length (see sqlblog.org/2009/10/09/…). (3) Be careful with NOLOCK all over the place. This is not a magical turbo button, and can lead to incorrect results in a variety of ways. Commented Mar 31, 2014 at 19:22
  • Did not know the AS syntax was deprecated thanks. The NOLOCK is required in our environment due to the high CRUD requests being hit and is in place per our DBA. I, personally, try to stay away from it but such is life. Commented Mar 31, 2014 at 19:25
  • 3
    It's not the AS, it's the single quotes around aliases that's deprecated. Commented Mar 31, 2014 at 19:26
  • @Matthew at the very least, you should use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top, instead of NOLOCK littered throughout the query. At the very least, when your DBA learns about snapshot isolation, it's one change instead of <number of tables in query>. Commented Mar 31, 2014 at 19:31
  • 1
    @Matthew [like this]. Commented Mar 31, 2014 at 20:24

2 Answers 2

Reset to default
4

Assuming that your start date and end date are compared against DlvryDt in DeliveryOrder table, I suggest to use a very low date value for start date and a very high date value for end date in cases where these date variables are null. Please see following query. 

SELECT  do.OrdrNmbr AS 'Order Number',
        mb.BOLNmbr AS 'BOL Number',
        do.DlvryDt  AS 'Deliver Date',
        mh.Text3 AS 'Truck',
        do.DlvrdQntty AS 'Gallons Delivered',
        ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
    ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
    ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
    ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
   ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber
and do.DlvryDt >= ISNULL(@StartDate, '19000101')
and do.DlvryDt < ISNULL(dateadd(day, 1, @EndDate), '21000101');
Improve this answer
1
  • 1
    Couple of things: (1) this may not optimize so well if a plan is first generated for the dates being populated, then subsequent queries don't use them, and vice versa. (2) Should actually use < DATEADD(DAY, 1, @EndDate) to not lose any data from the end day after midnight. Commented Mar 31, 2014 at 19:30
2

I have tested Binaya Regmi's answer and it worked but I also ran across a blog posting that helped me develop, what I think, is a better solution.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

I added 

AND (do.DlvryDt >= @StartDate OR @StartDate IS NULL)
AND (do.DlvryDt <= @EndDate OR @EndDate IS NULL)

to the where clause and all works well.

Improve this answer
2
  • 1
    Why do you think that's different from Binaya's answer? Should boil down to the same execution plan. Personally I think the better approach is one where you build dynamic SQL based on the presence of parameters - this has the least chance of going horribly wrong due to different parameters, and you can use OPTION (RECOMPILE) to prevent wonkiness due to different parameter values too. Commented Mar 31, 2014 at 20:22
  • OR is often less performant Commented Sep 26, 2016 at 20:19

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.