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.

7
  • 3
    (1) Please be careful with AS 'alias' syntax - this is deprecated (see sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/…). (2) Be careful declaring types like VARCHAR without length (see sqlblog.com/blogs/aaron_bertrand/archive/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. Mar 31 '14 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. Mar 31 '14 at 19:25
  • 3
    It's not the AS, it's the single quotes around aliases that's deprecated. Mar 31 '14 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>. Mar 31 '14 at 19:31
  • 1
    @Matthew [like this]. Mar 31 '14 at 20:24
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');
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. Mar 31 '14 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.

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. Mar 31 '14 at 20:22
  • OR is often less performant
    – paparazzo
    Sep 26 '16 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.