How to set parameters to show data from a period of time in SQL?

user_12108263 0
Viewed 368

I created a SQL stored procedure:

CREATE PROCEDURE [stored procedure name]
(
    @Location [varchar](255),
    @start_date datetime,
    @end_date datetime
)
AS
BEGIN
    SELECT id, location, name, begin_date, age, career, phone_number
    FROM information
    WHERE (begin_date = @start_date OR @start_date IS NULL) 
    AND (begin_date = @end_date OR @end_date IS NULL)
    AND (location = @Location OR @Location = 'All')
END

My thought is to set @start_date and @end_date default to Null but it can also show specific data from a period of time if user select to do so, which means:

  1. When I run the following code:
    EXEC [stored procedure name] @start_date = NULL, @end_date = NULL, @Location = 'New York';

It will show all data from New York regardless of time.

  1. When I run the following code:
    EXEC [stored procedure name] @start_date = '2020-03-01', @end_date = '2020-03-31', @Location = 'New York';

It will show data from New York during March 1st, 2020 to March 31st, 2020. How can I adjust my code to achieve this goal? It seems that my code lacks a connection between @start_date and @end_date.

parameters stored-procedures tsql sql-server sql
2 Answers

You are pretty close - you just need to make use of greater than equal to (>=) and less than (<) instead of equals (=) for your comparisons.

SELECT id, location, name, begin_date, age, career, phone_number
FROM information
WHERE (begin_date >= @start_date or @start_date is null) 
And (begin_date < dateadd(day, 1, @end_date) or @end_date is null)
And (location = @Location or @Location = 'All')

Note 1: the dateadd(day, 1, @end_date) is to take into account any time part of begin_date, so you can set @end_date to 31 May 2020 and include any entries that fall on that day.

Note 2: Unless you wish to specify a time component of the incoming parameter I would use a date datatype to avoid any unexpected behaviour happening with this compare. In fact begin_date should probably also just be a date as I don't think you normally track the time that someone starts.

As an aside if you default your parameters e.g.

(
    @Location [varchar](255),
    @start_date datetime = null,
    @end_date datetime = null
)

You don't have to explicitly pass them in e.g.

EXEC [stored procedure name] @Location = 'New York';
user_11274280

Seems you missing the range check as you matching exact dates

    SELECT id, location, name, begin_date, age, career, phone_number
    FROM information
    WHERE (begin_date >= @start_date or @start_date is null) 
    And (begin_date <= @end_date or @end_date is null)
    And (location = @Location or @Location = 'All')
user_9252220
Related