compare dates with optional arguments in sproc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

compare dates with optional arguments in sproc

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2008-11-20 : 15:19:33

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-20 : 15:22:29

Look up COALESCE in BOL.

select 
station_no, sample_date, value 
from 
v_extract
where
sample_date >= COALESCE(@start_date, '19000101')
AND sample_date <= COALESCE(@end_date, '99991231')

-- Or
WHERE
sample_date  COALESCE(@start_date, '19000101') AND COALESCE(@end_date, '99991231')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-20 : 15:24:19

Is that correct or is the logic reversed?

quote:
Originally posted by dbenoit64

if both are specified then return data where @start_date >= sample_date >= @end_date

dbenoit64
Starting Member

36 Posts

Posted - 2008-11-24 : 10:18:05

Thanks for the reply.

The logic is correct where sample_date should be between the user specified start and end. And this does work but only if both the start and end date are specified. ie:

sp_get_data_date_test "NB01BL0046", "01-01-1960", "01-01-1968"

works. But

sp_get_data_date_test "NB01BL0046", NULL, NULL

does not return any records. (should return all).

oh and the block:

WHERE
sample_date COALESCE(@start_date, '19000101') AND COALESCE(@end_date, '99991231')

gives me an error: Incorrect syntax near the keyword 'COALESCE'....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:38:02

sample_date what?
I think you want to check if the sample_date is greater, lesser or between the other two dates, right?

E 12°55'05.63"
N 56°04'39.26"

dbenoit64
Starting Member

36 Posts

Posted - 2008-11-24 : 10:50:23

Okay it was actually correct. I was setting the values to 0 length strings so it wasn't picking up the nulls.

THanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-24 : 12:53:15

quote:
Originally posted by dbenoit64

oh and the block:

WHERE
sample_date BETWEEN COALESCE(@start_date, '19000101') AND COALESCE(@end_date, '99991231')

gives me an error: Incorrect syntax near the keyword 'COALESCE'....

Sounds like you figured it out, but I left out the BETWEEN operator.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources