1) What is the code (with "with" loop) you have problems with ?

2) The procedure does not have SUSPEND; so it is execute procedure.
Either call it EXECUTE PROCEDURE ..., or add SUSPEND at the end.

3) This procedure does not give correct results anyway ...
(try 1.1.2004, which is thursday)

Ivan
http://www.volny.cz/iprenosil/interbase

> I have a problem.
> I can't use while do loop in stored procedure.
> There is an example in interbase 5 book for while do loop.
> When I run this stored procedure, I get a message that ('Unknown Error')
>
> I fined this procedure in the mail list.
> I compiled it.
> And I Run it.
>
> Select * from Get_Week ('01.01.2001')
> I get a massage that ('Unknown error').
> And there isn't any result set.
>
> CREATE PROCEDURE GET_WEEK
> (
> D TIMESTAMP
> )
> RETURNS
> (
> WEEK_NO VARCHAR(8)
> )
> AS
> declare variable DAYNUM INTEGER;
> declare variable WK1BEGINS DATE;
> declare variable YEARNUM INTEGER;
> declare variable YEAR_BEGINS DATE;
> declare variable WEEKSTR CHAR(2);
> declare variable WEEKNUM integer;
> declare variable WEEK1_FOUND char(1);
> BEGIN
> YEARNUM = EXTRACT (YEAR FROM D);
> WEEK1_FOUND = 'F';
> WHILE (WEEK1_FOUND <> 'T') DO
> BEGIN
> WK1BEGINS = CAST('01/01/'||CAST(YEARNUM AS CHAR(4)) AS DATE);
> while (EXTRACT(WEEKDAY FROM WK1BEGINS) <> 1) DO
> WK1BEGINS = WK1BEGINS + 1;
> IF (D < WK1BEGINS) THEN
> YEARNUM = YEARNUM -1;
> ELSE
> WEEK1_FOUND = 'T';
> END
> WEEKNUM = CAST(((D-WK1BEGINS)/7)+0.5 AS INTEGER);
> IF (WEEKNUM < 10) THEN
> WEEKSTR = '0'||CAST(WEEKNUM AS CHAR(1));
> ELSE
> WEEKSTR = CAST(WEEKNUM AS CHAR(2));
> WEEK_NO = CAST(YEARNUM AS CHAR(4))||'/'||WEEKSTR;
> END