optional datetime parameter in stored procedure

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 2000 Forums

Transact-SQL (2000)

optional datetime parameter in stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-27 : 12:07:35

Marichelle writes "Hi there,

I have 6 optional parameters in my stored procedure, 1 of which is a datetime type. I need to know how to make this optional (ie, if empty, don't query on this field). Also, user should be able to pass this parameter as >=, >, <, <=, between (date1) and (date2).

I have the following:
It works but I need to figure out how to accept and process if there are operators such as the >, <, etc.

ALTER PROCEDURE TimeSheet
(
@company_name varchar(50) = "%",
@employee_name varchar(50)= "%",
@work_no varchar(50)= "%",
@work_type varchar(50)= "%",
@time_type varchar(50)= "%",
@work_date datetime = NULL
)

AS

select company_name, employee_name, work_no, work_type, time_type, work_date, hours
from v_employee_timesheet
WHERE company_name LIKE @company_name AND employee_name LIKE @employee_name
AND work_no LIKE @work_no AND work_type LIKE @work_type AND time_type LIKE @time_type
AND (@work_date IS NULL) OR (work_date = @work_date)

Please help! Thanks!"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-27 : 12:28:19

Do not double or cross post.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62432

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 13:05:27

We pass "optional" parameters to SProcs as NULL. You could also make the @work_date a varchar, and allow it to be a blank string:


ALTER PROCEDURE TimeSheet 
(
...
    @work_date varchar(24) = NULL
)
...
DECLARE @work_date_DT datetime   -- The Actual DateTime variable ...
SELECT @work_date_DT = CONVERT(datetime, NullIf(@work_date, ''))

"user should be able to pass this parameter as >=, >, <, <=, between (date1) and (date2)"

We do this with a MIN and MAX:


ALTER PROCEDURE TimeSheet 
(
...
    @work_date_MIN varchar(24) = NULL,
    @work_date_MAX varchar(24) = NULL,
...
)
...
DECLARE @work_date_MIN_DT datetime,   -- The Actual DateTime variable ...
        @work_date_MAX_DT datetime
SELECT @work_date_MIN_DT = CONVERT(datetime, NullIf(@work_date_MIN, '')),
       @work_date_MAX_DT = CONVERT(datetime, NullIf(@work_date_MAX, ''))
...
SELECT ...
FROM v_employee_timesheet 
WHERE 
    ...
    AND (@work_date_MIN_DT IS NULL OR work_date >= @work_date_MIN_DT)
    AND (@work_date_MAX_DT IS NULL OR work_date <= @work_date_MAX_DT)

that way the user can leave either the MIN or MAX date blank to get just a Start or End date, or fill in both for a range. (Doesn't quite cover the range that you described, but I hopefully its food for thought)

"AND (@work_date IS NULL) OR (work_date = @work_date)"

Note that you are mixing AND and OR at the same indentation level here, which is going to give you some unexpected results! You need:


AND ((@work_date IS NULL) OR (work_date = @work_date))

although the brackets around the individual tests are not strictly speaking required (but do no harm!)

MVJ: Could I suggest only marking one of the the duplicates? Otherwise I'm not sure which one to post an answer against, and Madhi will probably be posting against the other one as I type this

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources