И вновь о вызове хранимой из Аксапты

[Protey]

Без проблем, если вам будет не лень копаться буду признателен

Вот метод run из аксапты

void run()
{
Connection connection;
Statement stmt;
ResultSet resultSet;
str ds;
str de;
str sqlS;
str d1;
str d2;
;

connection = new Connection();
stmt = connection.createStatement();
d1 = date2str(PeriodBegin,123,2,2,2,2,4);
d2 = date2str(PeriodEnd,123,2,2,2,2,4);
ds = substr(d1,7,4)+'-'+substr(d1,4,2)+'-'+substr(d1,1,2);
de = substr(d2,7,4)+'-'+substr(d2,4,2)+'-'+substr(d2,1,2);
sqlS = ("EXECUTE [AXTest].[dbo].[GET_DISC_CARD_REP] %1, %2, %3, %4, %5, %6, '%7', '%8'");
stmt.executeUpdate(strfmt(sqlS, Count_Buys0, Count_Buys1, Count_Buys2, Count_Buys3, Count_Buys4, Actual_Period, ds, de));
super();
}


вот сама процедура

работа прерывается на вот этой строке:
set @work = axtest.dbo.Is_worked(@cod, @count_buys0, @count_buys1, @count_buys2, @count_buys3, @count_buys4, @date_act1, @date_act2);

ALTER proc dbo.GET_DISC_CARD_REP(
@count_buys0 int,
@count_buys1 int,
@count_buys2 int,
@count_buys3 int,
@count_buys4 int,
@actual_period int,
@date_start1 varchar(20),
@date_end1 varchar(20))
as

delete from axtest.bmssa.disc_card_rep;

DROP TABLE ##DISC_CARD_REP_TMP;

CREATE TABLE ##DISC_CARD_REP_TMP (
[DISC_CODE] [varchar] (22) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
[PRODUCTION] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
[PAY_SUM] [numeric](28, 12) NOT NULL ,
[DATA] [datetime] NOT NULL ,
[GROUPS] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
[REP_DATE] [datetime] NULL ,
[IS_WORK] [int] NULL ,
[RECID] [int] IDENTITY (1, 1) NOT NULL ,
CHECK ([RECID] <> 0)
) ON [PRIMARY]

declare @date_start datetime;
declare @date_end datetime;
declare @date_act1 datetime;
declare @date_act2 datetime;
declare @date_rep1 datetime;
declare @date_rep2 datetime;
declare @cod varchar(22);
declare @work int;

set @date_start=cast(@date_start1 as datetime);
set @date_end=cast(@date_end1 as datetime);

set @date_rep1=@date_start;
set @date_rep2=dateadd(dd,-datepart(dd,@date_rep1),dateadd(mm,1,@date_rep1));

while @date_rep2<@date_end
begin
declare cur1 cursor
for select distinct(dc.DISC_CODE)
from axtest.dbo.discont_card dc
where dc.data between @date_rep1 and @date_rep2;

set @date_act1 = dateadd(dd,1-datepart(dd,@date_rep1),dateadd(mm,1-@actual_period,@date_rep1));
set @date_act2 = @date_rep2

open cur1;

fetch cur1 into @cod;
while @@FETCH_Status=0
begin
set @work = axtest.dbo.Is_worked(@cod, @count_buys0, @count_buys1, @count_buys2, @count_buys3, @count_buys4, @date_act1, @date_act2);
insert into ##DISC_CARD_REP_TMP(disc_code, production, pay_sum, data, groups)
(select dc.disc_code, dc.production, dc.pay_sum, dc.data, groups from axtest.dbo.discont_card dc
where dc.disc_code=@cod and dc.data between @date_rep1 and @date_rep2);
update ##DISC_CARD_REP_TMP
set is_work=@work,
rep_date=@date_rep1
where disc_code=@cod;
fetch cur1 into @cod;
end;

close cur1;

deallocate cur1;

set @date_rep1=dateadd(dd,1,@date_rep2);
set @date_rep2=dateadd(dd,-1,dateadd(mm,1,@date_rep1));

end


declare cur1 cursor
for select distinct(dc.DISC_CODE)
from axtest.dbo.discont_card dc
where dc.data between @date_rep1 and @date_end;

set @date_act1 = dateadd(dd,1-datepart(dd,@date_rep1),dateadd(mm,1-@actual_period,@date_rep1));
set @date_act2 = @date_rep2

open cur1;

fetch cur1 into @cod;

while @@FETCH_Status=0
begin
set @work = axtest.dbo.Is_worked(@cod, @count_buys0, @count_buys1, @count_buys2, @count_buys3, @count_buys4, @date_act1, @date_act2);
insert into ##DISC_CARD_REP_TMP(disc_code, production, pay_sum, data, groups)
(select dc.disc_code, dc.production, dc.pay_sum, dc.data, groups from axtest.dbo.discont_card dc
where dc.disc_code=@cod and dc.data between @date_rep1 and @date_end);
update ##DISC_CARD_REP_TMP
set is_work=@work,
rep_date=@date_rep1
where disc_code=@cod;
fetch cur1 into @cod;
end;

insert into axtest.bmssa.disc_card_rep(disc_code, production, pay_sum, data, groups, rep_date, is_work, recid)
(select * from ##DISC_CARD_REP_TMP);

close cur1;

deallocate cur1;


GO


ну вот и сама функция

ALTER FUNCTION Is_worked(
@code char(22),
@count_buys0 int,
@count_buys1 int,
@count_buys2 int,
@count_buys3 int,
@count_buys4 int,
@date_start datetime,
@date_end datetime)
returns int AS
begin

declare @is_work int
declare @date1 datetime
declare @date2 datetime

set @is_work = 0;
set @date1 = @date_start;
set @date2 = @date_start;

if @count_buys0 < (select count(*) from drest.dbo.discont_card d_card
where d_card.data <= @date_end and d_card.disc_code=@code) set @is_work = 1;

if @is_work = 0
begin
if @count_buys1 < (select count(*) from drest.dbo.discont_card d_card
where d_card.data between @date_start and @date_end and d_card.disc_code=@code) set @is_work = 1;
end;

if @is_work = 0
begin
if @count_buys2 < (select count(*) from drest.dbo.discont_card d_card
where d_card.data <= @date_start and d_card.disc_code=@code) set @is_work = 1;
end;

while (@date2 < @date_end) and (@is_work = 0)
begin
if @count_buys3 < (select count(*) from drest.dbo.discont_card d_card
where d_card.data between @date2 and dateadd(mm,1,@date2) and d_card.disc_code=@code) set @is_work = 1;
set @date2 = dateadd(mm,1,@date2);
end;

while (@date1 > (select min(dc1.data) from drest.dbo.discont_card dc1 where dc1.disc_code=@code) ) and (@is_work = 0)
begin
if @count_buys4 < (select count(*) from drest.dbo.discont_card d_card
where d_card.data between dateadd(mm,-1,@date1) and @date1 and d_card.disc_code=@code) set @is_work = 1;
set @date1 = dateadd(mm,-1,@date1);
end;

return @is_work;

end