hi, i am pretty new in sybase, so excuse if this is a dumb question

i dont know why this code does not execute:

drop procedure if exists FillDateRange;

create procedure FillDateRange()
begin

declare @dtFirstDay datetime;
declare @ii int;
declare @iNumDays int;

select FIRST_DAY, INT_NUM_OF_DATA_DAYS-1
into dtFirstDay, iNumDays
from TEST_DATA
where id = 999;

insert into DATE_REF (WORK_DATE, IDX) values (makedate(1900, 1),
0);

set @ii = -1;
while (@ii <= @iNumDays)
begin
insert into DATE_REF (WORK_DATE, IDX) values (dateadd(day, @ii,
dtFirstDay), @ii + 1);
set @ii = @ii + 1;
end;

end;

it gives me this error

[Error] Script lines: 1-24 -------------------------
SQL Anywhere Error -131: Syntax error near 'begin' on line 19
Msg: 102, Level: 15, State: 0
Line: 0

everything seems to be just right, except for the while; but that is
the right syntax i think

please help

I don't think this is the correct syntax for what you are trying to do.
What version of SQL Anywhere are you using?

In version 11, and possibly older versions this should work:

IF EXISTS(SELECT * FROM sysprocs WHERE procname = 'FillDateRange') THEN
DROP PROCEDURE FillDateRange;
ENDIF;

José Miguel Loor wrote:

The syntax of the WHILE statement is incorrect for Watcom-SQL. Here's the
replacement. You could likely eliminate the WHILE loop and use an INSERT ...
SELECT together with sa_rowgenerator().

create procedure FillDateRange()
begin

declare @dtFirstDay datetime;
declare @ii int;
declare @iNumDays int;

select FIRST_DAY, INT_NUM_OF_DATA_DAYS-1
into dtFirstDay, iNumDays
from TEST_DATA
where id = 999;

insert into DATE_REF (WORK_DATE, IDX) values (makedate(1900, 1), 0);

set @ii = -1;
while (@ii <= @iNumDays) loop
insert into DATE_REF (WORK_DATE, IDX) values (dateadd(day, @ii,
dtFirstDay), @ii + 1);
set @ii = @ii + 1;
end loop;
end;

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




José Miguel Loor wrote:

Bruce Hay wrote:
I also just noticed from reading Bruce's reply that you declare
variables named @dtFirstDay and @iNumDays, but your select statement
from TEST_DATA inserts into variable names that do not use the @ symbol.
Your select statement should likely read :

select FIRST_DAY, INT_NUM_OF_DATA_DAYS-1
into @dtFirstDay, @iNumDays
from TEST_DATA
where id = 999;



--
Reg Domaratzki, Sybase iAnywhere Solutions
Please reply only to the newsgroup

Documentation : Exercise your WRITE @DocCommentXchange: DCX.sybase.com
SQL Anywhere Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere
-> Optionally set filter to "Display ALL platforms IN ALL MONTHS"