fb数据库的存储过程,i是一个变量,K是一个传入的参数,:tzhuyi也是一个传入的日期参数,以下代码希望在一段时期内插入或更新rj表的zhuyi字段。
create or alter procedure NEW_PROCEDURE (
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date)
as
declare variable I integer;
begin
i=0;
while (i<=k) do
begin
if (exists (select shijian from rj where shijian=:tzhuyi+i)) then //这里出了问题。
insert into rj (shijian, jilu, zhuyi) values (:shijian,:jilu,:zhuyi);
else
update rj
set zhuyi=:zhuyi;
i=i+1;
end
end
Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
I.
At line 13, column 58.改成下面这样,还是不行。
create or alter procedure NEW_PROCEDURE (
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date not null)
as
declare variable I integer;
begin
i=0;
while (i<=k) do
begin
if (exists (select shijian from rj where shijian=:Tzhuyi+i)) then
insert into rj (shijian, jilu, zhuyi) values (:shijian,:jilu,:zhuyi);
else
update rj
set zhuyi=:zhuyi
where
shijian=:shijian;
i=i+1;
end
endColumn does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
I.
At line 13, column 58.
create or alter procedure NEW_PROCEDURE (
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date)
as
declare variable I integer;
begin
i=0;
while (i<=k) do
begin
if (exists (select shijian from rj where shijian=:tzhuyi+i)) then //这里出了问题。
insert into rj (shijian, jilu, zhuyi) values (:shijian,:jilu,:zhuyi);
else
update rj
set zhuyi=:zhuyi;
i=i+1;
end
end
Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
I.
At line 13, column 58.改成下面这样,还是不行。
create or alter procedure NEW_PROCEDURE (
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date not null)
as
declare variable I integer;
begin
i=0;
while (i<=k) do
begin
if (exists (select shijian from rj where shijian=:Tzhuyi+i)) then
insert into rj (shijian, jilu, zhuyi) values (:shijian,:jilu,:zhuyi);
else
update rj
set zhuyi=:zhuyi
where
shijian=:shijian;
i=i+1;
end
endColumn does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
I.
At line 13, column 58.
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date 改为:
@TZHUYI date,
@K integer,
@ZHUYI varchar(10),
@JILU varchar(10),
@SHIJIAN date
付值后再PRINT一下试试
if (exists (select shijian from rj where shijian=:tzhuyi+i)) then //这里出了问题。
这里的shijian应该是表的字段吧,但是传入的参数也有个SHIJIAN,可能冲突了。create or alter procedure NEW_PROCEDURE (
@TZHUYI date,
@K integer,
@ZHUYI varchar(10),
@JILU varchar(10),
@SHIJIAN date)
as
declare variable I integer;
declare @tableshijian date;
begin
i=0;
while (i <=k) do
begin
select @tableshijian = shijian from rj where shijian=@tzhuyi+1
if @@rowcount > 0
begin
insert into rj (shijian, jilu, zhuyi) values (@tableshijian,@jilu,@zhuyi);
end
else
update rj set zhuyi=@zhuyi;
i=i+1;
end
end 不知道我写的对不对哦
把表结构贴出来
alter procedure NEW_PROCEDURE (
@TZHUYI date,
@K integer,
@ZHUYI varchar(10),
@JILU varchar(10),
@SHIJIAN date)
as
declare @i integer;
begin
set@i=0
while (@i <= @k) do
begin
if (exists (select shijian from rj where shijian=@tzhuyi+@i)) then //这里出了问题。
insert into rj (shijian, jilu, zhuyi) values (@shijian,@jilu,@zhuyi)
end
else
begin
update rj
set zhuyi=@zhuyi
@i=@i+1
end
end
由于不知道你具体有什么条件,所以只能就你给出的存储过程来改!