Adam,

Comments in-line.....

At 07:57 AM 20/11/2006, you wrote:

>When I set "StartTime" and "EndTime" variable like const char
>/*"StartTime" = '17:15:00.0000';
>"EndTime" = '18:15:00.0000';*/ it work (I get 5 results), but if I
>have dinamic sign with the same values it doesn't work (I get
>null)....please help me!
>
>select condition is:
>...
>cast("Visit"."Time" as time) between cast(:"StartTime" as time)and
>cast(:"EndTime" as time)
>
>This is the code example:
>
>//////////////////////PROCEDURE CODE/////////////////////////////
>
>declare variable "StartTime" char(13);
>declare variable "EndTime" char(13);

These are strings.....

>...
>
>/*this don't work!!! I'm shure I have '17:15:00.0000' and
>'18:15:00.0000' as result of previous query*/
>"StartTime" = cast(:"StartHour" as char(2)) || ':' ||
>cast(:"StartMinute" as char(2)) || ':' || '00.0000';
>"EndTime" = cast(:"EndHour" as char(2)) || ':' || cast(:"EndMinute" as
>char(2)) || ':' || '00.0000';this don't work!!!
>
>/*this work!!!*/
>/*"StartTime" = '17:15:00.0000';
>"EndTime" = '18:15:00.0000';*/

These are date literals.

A date literal can be passed as a constant but you can't replace a
constant with a string variable. Datetime types are not strings!!

You didn't show us your stored procedure declaration but I suppose
that you are passing in "StartDate" as Date and the four other
variables as integers. This is unnecessary and, well, wrong for what
you're trying to do. (It *is* possible to construct a datetime
literal as part of an EXECUTE STATEMENT string but that's cumbersome
and won't work for what you're doing here anyway.)

Pass in Time type variables for StartTime and EndTime. You *can*
pass these as literals.

Pardon me for not following your practice of double-quoting variables
and stored procedure arguments. This is a daft practice.

Another daft practice is using COUNT as an existence test, and your
procedure logic is...well...serpentine...

create procedure XXXX (
StartDate Date,
EndDate Date,
StartTime Time,
EndTime Time,
idInstructor integer)
returns (
InstructorActivityHours integer,
.....)
as
begin
InstructorActivityHours = 0;
FOR
select 1 from "Visit"
where
"idInstructor" = :idInstructor
and cast("Time" as Date) = :StartDate
and cast("Time" as Time) between :StartTime and :EndTime
DO
InstructorActivityHours = InstructorActivityHours + 1;
....
end

./heLen