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:
- 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.
- 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
.
Advertisement
Answer
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';