Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256

    Unanswered: Dealing with optional variables

    I'm looking for opinions here:

    I have a stored procedure that has one required variable, and two optional variables, like this:

    CREATE PROCEDURE sp_tariff_rule
    @tariff_id INT,
    @start_date DATETIME = NULL,
    @end_date DATETIME = NULL
    ...etc...

    I want the procedure to process
    1) all data is no dates are presented
    2) all data after the start date, if no end date is supplied
    3) all data before the end date if no start date is supplied
    4) all data between the start and end dates if both are supplied

    Now, instead of an elaborate conditional, I added this to the WHERE clause of my SQL statement:

    AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date))

    It works fine, but I want to know if anyone has a different/better way of doing it, or if there is a big bug waiting to happen here.

    I typically don't like to create multipurpose routines in my code, but this is a better approach for my in a non-object-oriented world of SQL.
    -bpd

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Dealing with optional variables

    Simply use Where service_date between @start_date and @end_date

    Default @start_date to '19000101' if null
    Default @end_date to '99991231' if null

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •