| Stored Procedure problem [message #374597] |
Wed, 20 June 2001 14:01 |
van
Messages: 6
Registered: June 2001
|
Junior Member |
|
|
the stored procedure below works like a charm when i get a false value - result = 22674, but i only get back <DEFAULT> for my output values when the result = 31771 return should happen (even result = <DEFAULT>)
any ideas? i'm a SQL guy, so i'm completely stupid as a sack of hammers when it comes to oracle
TIA,
Van
CREATE OR REPLACE PROCEDURE bbq (res_id IN NUMBER,
start_date IN DATE,
end_date IN DATE,
off IN NUMBER,
start_date1 OUT DATE,
end_date1 OUT DATE,
result OUT NUMBER)
IS
rowcount number(10);
BEGIN
select count(*) into rowcount From ScheduleDetails
Where ResourceID = res_id and (
(
ScheduleStartTimeDate = start_date + ((1/24) * off)
)
or
(
end_date + ((1/24) * off) > ScheduleStartTimeDate
and
end_date + ((1/24) * off) <= ScheduleEndTimeDate
)
or
(
start_date + ((1/24) * off) > ScheduleStartTimeDate
and
start_date + ((1/24) * off) < ScheduleEndTimeDate
)
or
(
start_date + ((1/24) * off) < ScheduleStartTimeDate
and
end_date + ((1/24) * off) > ScheduleEndTimeDate
)
);
IF rowcount > 0 THEN
result := 31771;
start_date1 := null;
end_date1 := null;
ELSE
result := 22674;
start_date1 := start_date + ((1/24) * off);
end_date1 := end_date + ((1/24) * off);
END IF;
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
