Optional DateTime parameter stored procedure.. HELPPP!
-
Hi there,
I've been playing around with this for hours now.. and I've finally decided to ask for help. I"m new to stored procedures as you might tell.. Please help!!
CREATE PROCEDURE
TimeSheet
(
@company_name
varchar(50) = '%',
@employee_name
varchar(50)= '%',
@work_no
varchar(50)= '%',
@work_type
varchar(50)= '%',
@time_type
varchar(50)= '%',
@work_date1
datetime = NULL,
@work_date2
datetime = NULL,
@date_operator
as string = '='
)
AS
IF
@work_date1 IS NULL AND @work_date2 IS NULL
BEGIN
SELECT company_name, employee_name, work_no, work_type, time_type, work_date, hours
FROM employee_times
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
END
ELSE
BEGIN
SELECT company_name, employee_name, work_no, work_type, time_type, work_date, hours
FROM employee_times
WHERE
CASE @date_operator
WHEN '='
THEN (work_date = @work_date1)
WHEN '>='
THEN (work_date >= @work_date1)
WHEN 'AND'
THEN (work_date >= @work_date1 AND work_date <= @work_date2)
WHEN '<='
THEN (work_date <= @work_date1)
WHEN '<'
THEN (work_date < @work_date1)
WHEN '>'
THEN (work_date > @work_date1)
END
AND 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
END
-
CREATE PROCEDURE TimeSheet
(
@company_name varchar(50) = '%',
@employee_name varchar(50)= '%',
@work_no varchar(50)= '%',
@work_type varchar(50)= '%',
@time_type varchar(50)= '%',
@work_date1 datetime = NULL,
@work_date2 datetime = NULL,
@date_operator as varchar(2000) = '='
)
AS
SET NOCOUNT ON /*skip a roundtrip*/
IF (@work_date1 IS NULL AND @work_date2 IS NULL)
BEGIN
SELECT company_name, employee_name, work_no, work_type, time_type, work_date, hours
FROM employee_times
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
END
ELSE
BEGIN
IF @date_operator IN ('=','>=','<=','>','<','AND') /*valid operator*/
SELECT company_name, employee_name, work_no, work_type, time_type, work_date, hours
FROM employee_times
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 ( /*not sure if fully dynamic sql is better*/
(@date_operator='=' AND work_date = @work_date1 )
OR (@date_operator='>=' AND work_date >= @work_date1)
OR (@date_operator= 'AND' AND work_date >= @work_date1 AND work_date <= @work_date2)
OR (@date_operator ='<=' AND work_date <= @work_date1)
OR (@date_operator='<' AND work_date < @work_date1)
OR (@date_operator='>' AND work_date > @work_date1)
)
END
-
If a parameter can be NULL, I make the parameter default to NULL and then use a WHERE clause like this:
WHERE (BeginDateTime >=
CASE WHEN @BDateTime IS NULL
THEN BeginDateTime
ELSE @BDateTime END
AND EndDateTime >=
CASE WHEN @EDateTime IS NULL
THEN EndDateTime
ELSE @EDateTime END)
AND LName =
CASE WHEN @LName IS NULL
THEN LName
ELSE @LName END
That first checks to see if the parameter is NULL. If so, then it selects all of that column (example: WHERE LName = LName). If it is not NULL, then it uses the parameter.
-SQLBill
-
BTW-you don't say what problem you are having....so....
Remember, if you have a parameter then you must input SOMETHING.
EXEC @company_name = 'MyComp',
@employee_name = 'Smith',
@work_no = '123',
@work_date1 = '2006-02-27 08:00',
@work_date2 = '2006-02-27 17:00'
or
EXEC 'MyComp', 'Smith', '123', NULL, NULL, '2006-02-27 08:00', '2006-02-27 17:00'
Including the parameter name allows you to 'skip' fields when there isn't any value to enter (such as NULL). If you don't use the parameter name, you must include an entry for every parameter (except for the last one(s) if they are NULL. - That's why I don't include the parameter for date_operator in the last example...it's NULL.
-SQLBill
-
This one worked for me!! Thank you so much to all who replied especially Jo Pattyn who made this correction
! I'll try the other method too. You're lifesavers.. thanks!!! btw.. the problem i was having was it was giving me an "error on '=' near END".. something like that.. THANKS AGAIN!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply