执行结果总是只有一条记录产生影响,后来插入的都是一些重复的记录,过程错在哪?
在SQL SERVER 2000中的存储过程如下:
CREATE PROCEDURE PRO_DEPOT_REPORT(@P_months int,@P_years int) AS
declare @RECORDCOUNT integer,
@part_id Varchar(50),
@num integer,
@price money,
@IN_PART_NUM INT,
@OUT_PART_NUM INTselect @RECORDCOUNT=count(part_id)
from tb_depot_report
where months=@P_months and years=@P_years
if (@RECORDCOUNT is null) or (@RECORDCOUNT<1)
begin
declare cur_t cursor for
select part_id,num,price from tb_depot ;
open cur_t;
Fetch next from cur_t into @part_id,@num,@price
while(@@Fetch_status = 0)
begin
select @IN_PART_NUM=SUM(NUM) from TB_INPART where PART_ID = @PART_ID and MONTH(IN_DATE)=@P_months and YEAR(IN_DATE)=@P_years;
select @OUT_PART_NUM=FACT_NUM from TB_OUTPART where PART_ID=@PART_ID and MONTH(OUT_DATE)=@P_months and YEAR(OUT_DATE)=@P_years;
if @IN_PART_NUM is null
select @IN_PART_NUM= 0
if @OUT_PART_NUM is null
select @OUT_PART_NUM= 0
INSERT INTO TB_DEPOT_REPORT
(PART_ID, MONTHS, YEARS, NUM, PRICE, INPUT_NUM, SELL_NUM)
VALUES (@PART_ID, @P_months,@P_years,@NUM,@price,@IN_PART_NUM,@OUT_PART_NUM)
end
close cur_t;
deallocate cur_t;
end
GO
在SQL SERVER 2000中的存储过程如下:
CREATE PROCEDURE PRO_DEPOT_REPORT(@P_months int,@P_years int) AS
declare @RECORDCOUNT integer,
@part_id Varchar(50),
@num integer,
@price money,
@IN_PART_NUM INT,
@OUT_PART_NUM INTselect @RECORDCOUNT=count(part_id)
from tb_depot_report
where months=@P_months and years=@P_years
if (@RECORDCOUNT is null) or (@RECORDCOUNT<1)
begin
declare cur_t cursor for
select part_id,num,price from tb_depot ;
open cur_t;
Fetch next from cur_t into @part_id,@num,@price
while(@@Fetch_status = 0)
begin
select @IN_PART_NUM=SUM(NUM) from TB_INPART where PART_ID = @PART_ID and MONTH(IN_DATE)=@P_months and YEAR(IN_DATE)=@P_years;
select @OUT_PART_NUM=FACT_NUM from TB_OUTPART where PART_ID=@PART_ID and MONTH(OUT_DATE)=@P_months and YEAR(OUT_DATE)=@P_years;
if @IN_PART_NUM is null
select @IN_PART_NUM= 0
if @OUT_PART_NUM is null
select @OUT_PART_NUM= 0
INSERT INTO TB_DEPOT_REPORT
(PART_ID, MONTHS, YEARS, NUM, PRICE, INPUT_NUM, SELL_NUM)
VALUES (@PART_ID, @P_months,@P_years,@NUM,@price,@IN_PART_NUM,@OUT_PART_NUM)
end
close cur_t;
deallocate cur_t;
end
GO
,你在while循环里没有移动游标!
在
while(@@Fetch_status = 0)
begin
--你的语句
Fetch next from cur_t into @part_id,@num,@price ---在后面加上!
end