November 20, 2008 at 1:18 pm
I have a stored procedure im working on but not sure how to code this algroithm i have for the dates. any input would be very appreciated.
CREATE PROCEDURE sp_get_stuff
(
@station_list varchar(255) = NULL,
@start_date smalldatetime(4) = NULL,
@end_date smalldatetime(4) = NULL
)
AS
BEGIN
SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')
SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')
SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')
select
station_no, sample_date, value
from
v_extract
where
--1. STATION NUMBER: IN Statement (makes station_no an optional value)
((LEN(@station_list) = 0) OR (station_no IN (SELECT * FROM udf_SplitStrings(@station_list, ',')))) AND
--2. SAMLPE DATE ALGORITHM (want both start and end to be optional as well):
if only @start_date is specified then return data where @start_date >= sample_date
if only @end_date is specified then return data where sample_date <= @end_date
if both are specified then return data where @start_date >= sample_date >= @end_date
if both dates equal '' (empty string) then ignore sample dates (therefore both are "optional values")
order by station_no
END
November 20, 2008 at 1:58 pm
First, I'm not sure why you're doing the COALESCE RTRIM LTRIM on @station_list 3 times, and since @start_date and @end_date are smalldatetime, it wouldn't make sense to try to set these to empty string.
Where I have optional parameters, I've just typically done something like below:
SELECT ...
WHERE ...
AND (@start_date IS NULL OR @start_date <= sample_date)
AND (@end_date IS NULL OR sample_date <= @end_date)
November 24, 2008 at 8:25 am
YOURE RIGHT. THIS WORK GREAT.
THANKS
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply