在数据库中建立存储过程,可以执行,并生成了结果集,在delphi 中调用时总是显示”“Commandtext does not return a result set”,请高手指教,以下是存储过程 :ALTER procedure card_y_physic_month_prn
(@month varchar(10),
@store varchar(2),
@ptype varchar(2))
as
begin
declare @errcode int
begin transaction
save tran t_inner declare @dtBegin datetime
declare @dtEnd datetime
declare @sYear varchar(4)
declare @sMonth varchar(2)
declare @iMonth int
declare @iYear int
declare @iNextYear int
declare @iNextMonth int
declare @iserial int
declare @dLast decimal(7,2)
declare @dIn decimal(7,2)
declare @dOut decimal(7,2)
declare @dStock decimal(7,2) declare @sPhysicno varchar(10)
declare @sName varchar(40)
declare @sUnit varchar(4)
declare @sTypeNo varchar(2)
declare @sTypeName varchar(20) select @iserial=0
select @dIn=0
select @dOut=0
select @dStock=0 declare @dtLastCheck datetime
declare @sLastcheck varchar(8)
declare @sbillinno varchar(8)
declare @srecipeno varchar(8)
declare @dIndetail decimal(7,2)
declare @dOutdetail decimal(7,2)
select @dIndetail=0
select @dOutdetail=0--确定统计起始日期
select @sYear=substring(@month,1,4)
select @sMonth=substring(@month,5,2)
select @dtBegin=Convert(datetime,@sYear+'-'+@sMonth+'-'+'01',102);
select @iYear=Year(@dtBegin);
select @iMonth=Month(@dtBegin);
if @iMonth=12
begin
select @iNextYear=@iYear+1;
select @iNextMonth=1
end
else
begin
select @iNextYear=@iYear
select @iNextMonth=@iMonth+1
end
select @dtEnd=Convert(Datetime,cast(@iNextYear as char(4))+'-'+cast(@iNextMonth as char(2))+'-'+'01')--创建临时表
if exists(select * from dbo.sysobjects where name='VPHYSIC_MONTH' and xtype='U') drop table VPHYSIC_MONTH create table VPHYSIC_MONTH(
VSerier numeric(3,0) null,
VMonth varchar(10) null,
VStore varchar(2) null,
VPhysic varchar(10) null,
VName varchar(40) null,
VUnit varchar(4) null,
VType varchar(2) null,
VTypename varchar(20) null,
VLastCheck decimal(7,2) null,
VIn decimal(7,2) null,
VOut decimal(7,2) null,
VStock decimal(7,2) null,
VCheck decimal(7,2) null,
VDiff decimal(7,2) null)--取出药品列表
declare cur_physic cursor for
select a.physic_no,a.physic_name+a.physic_specs,a.scatter_unit,a.physic_type,b.type_name
from y_physic_material a,y_physic_type b
where a.physic_type=b.type_no
open cur_physic
fetch next from cur_physic into @sPhysicno,@sName,@sUnit,@sTypeno,@sTypename
while @@fetch_status=0
begin
select @iSerial=@iSerial+1--从盘点表中读取上月结存数
select @dtLastCheck=max(check_date) from y_check where check_date<@dtBegin
and drugstore_no=@store
select @sLastCheck=check_no from y_check where check_date=@dtLastCheck
and drugstore_no=@store
select @dLast=check_after from y_check_detail where check_no=@sLastCheck
and physic_no=@sPhysicno--从入库单中统计入库数量
declare cur_in cursor for
select bill_no from y_bill_in where bill_date>=@dtBegin and bill_date<@dtEnd
and cancel_flag='0' and drugstore_no=@store
open cur_in
fetch next from cur_in into @sbillinno
select @dIn=0
while @@fetch_status=0
begin
if exists(select quantity from y_bill_detail where bill_no=@sbillinno and physic_no=@sPhysicno)
begin
select @dIndetail=quantity from y_bill_detail where bill_no=@sbillinno and physic_no=@sPhysicno
select @dIn=@dIn+@dIndetail
end
fetch next from cur_in into @sbillinno
end
close cur_in
deallocate cur_in--从处方表中统计本月出库数量
declare cur_out cursor for
select recipe_no from y_recipe where recipe_date>=@dtBegin and recipe_date<@dtEnd
and cancel_flag='0' and drugstore_no=@store
open cur_out
fetch next from cur_out into @srecipeno
select @dOut=0;
while @@fetch_status=0
begin
if exists(select physic_quantity from y_recipe_detail where recipe_no=@srecipeno and physic_no=@sPhysicno)
begin
select @dOutDetail=physic_quantity from y_recipe_detail where recipe_no=@srecipeno and physic_no=@sPhysicno
select @dOut=@dOut+@dOutdetail
end
fetch next from cur_out into @srecipeno
end
close cur_out
deallocate cur_out--从库存表中读取当前库存
select @dStock=physic_quantity from y_stock where drugstore_no=@store and physic_no=@sPhysicno--将统计数据插入临时表中 insert into VPHYSIC_MONTH(VSerier,VMonth,VStore,VPhysic, VName, VUnit, VType, VTypename, VLastCheck,
VIn, VOut, VStock, VCheck, VDiff)
values(@iSerial,@month,@store,@sPhysicno,@sName,@sUnit,@sTypeno,@sTypename,@dLast,@dIn,@dOut,@dStock,0,0)
if @@ERROR<>0 and @@error <> -1
begin
set @errcode= 0
goto Failure
end fetch next from cur_physic into @sPhysicno,@sName,@sUnit,@sTypeno,@sTypename
end close cur_physic
deallocate cur_physicif @ptype<>'' select * from VPHYSIC_MONTH where VTYPE=@ptype
else select * from VPHYSIC_MONTH
commit tran
return(1)Failure:
rollback tran t_inner
commit tran
return(@errcode)
end;
(@month varchar(10),
@store varchar(2),
@ptype varchar(2))
as
begin
declare @errcode int
begin transaction
save tran t_inner declare @dtBegin datetime
declare @dtEnd datetime
declare @sYear varchar(4)
declare @sMonth varchar(2)
declare @iMonth int
declare @iYear int
declare @iNextYear int
declare @iNextMonth int
declare @iserial int
declare @dLast decimal(7,2)
declare @dIn decimal(7,2)
declare @dOut decimal(7,2)
declare @dStock decimal(7,2) declare @sPhysicno varchar(10)
declare @sName varchar(40)
declare @sUnit varchar(4)
declare @sTypeNo varchar(2)
declare @sTypeName varchar(20) select @iserial=0
select @dIn=0
select @dOut=0
select @dStock=0 declare @dtLastCheck datetime
declare @sLastcheck varchar(8)
declare @sbillinno varchar(8)
declare @srecipeno varchar(8)
declare @dIndetail decimal(7,2)
declare @dOutdetail decimal(7,2)
select @dIndetail=0
select @dOutdetail=0--确定统计起始日期
select @sYear=substring(@month,1,4)
select @sMonth=substring(@month,5,2)
select @dtBegin=Convert(datetime,@sYear+'-'+@sMonth+'-'+'01',102);
select @iYear=Year(@dtBegin);
select @iMonth=Month(@dtBegin);
if @iMonth=12
begin
select @iNextYear=@iYear+1;
select @iNextMonth=1
end
else
begin
select @iNextYear=@iYear
select @iNextMonth=@iMonth+1
end
select @dtEnd=Convert(Datetime,cast(@iNextYear as char(4))+'-'+cast(@iNextMonth as char(2))+'-'+'01')--创建临时表
if exists(select * from dbo.sysobjects where name='VPHYSIC_MONTH' and xtype='U') drop table VPHYSIC_MONTH create table VPHYSIC_MONTH(
VSerier numeric(3,0) null,
VMonth varchar(10) null,
VStore varchar(2) null,
VPhysic varchar(10) null,
VName varchar(40) null,
VUnit varchar(4) null,
VType varchar(2) null,
VTypename varchar(20) null,
VLastCheck decimal(7,2) null,
VIn decimal(7,2) null,
VOut decimal(7,2) null,
VStock decimal(7,2) null,
VCheck decimal(7,2) null,
VDiff decimal(7,2) null)--取出药品列表
declare cur_physic cursor for
select a.physic_no,a.physic_name+a.physic_specs,a.scatter_unit,a.physic_type,b.type_name
from y_physic_material a,y_physic_type b
where a.physic_type=b.type_no
open cur_physic
fetch next from cur_physic into @sPhysicno,@sName,@sUnit,@sTypeno,@sTypename
while @@fetch_status=0
begin
select @iSerial=@iSerial+1--从盘点表中读取上月结存数
select @dtLastCheck=max(check_date) from y_check where check_date<@dtBegin
and drugstore_no=@store
select @sLastCheck=check_no from y_check where check_date=@dtLastCheck
and drugstore_no=@store
select @dLast=check_after from y_check_detail where check_no=@sLastCheck
and physic_no=@sPhysicno--从入库单中统计入库数量
declare cur_in cursor for
select bill_no from y_bill_in where bill_date>=@dtBegin and bill_date<@dtEnd
and cancel_flag='0' and drugstore_no=@store
open cur_in
fetch next from cur_in into @sbillinno
select @dIn=0
while @@fetch_status=0
begin
if exists(select quantity from y_bill_detail where bill_no=@sbillinno and physic_no=@sPhysicno)
begin
select @dIndetail=quantity from y_bill_detail where bill_no=@sbillinno and physic_no=@sPhysicno
select @dIn=@dIn+@dIndetail
end
fetch next from cur_in into @sbillinno
end
close cur_in
deallocate cur_in--从处方表中统计本月出库数量
declare cur_out cursor for
select recipe_no from y_recipe where recipe_date>=@dtBegin and recipe_date<@dtEnd
and cancel_flag='0' and drugstore_no=@store
open cur_out
fetch next from cur_out into @srecipeno
select @dOut=0;
while @@fetch_status=0
begin
if exists(select physic_quantity from y_recipe_detail where recipe_no=@srecipeno and physic_no=@sPhysicno)
begin
select @dOutDetail=physic_quantity from y_recipe_detail where recipe_no=@srecipeno and physic_no=@sPhysicno
select @dOut=@dOut+@dOutdetail
end
fetch next from cur_out into @srecipeno
end
close cur_out
deallocate cur_out--从库存表中读取当前库存
select @dStock=physic_quantity from y_stock where drugstore_no=@store and physic_no=@sPhysicno--将统计数据插入临时表中 insert into VPHYSIC_MONTH(VSerier,VMonth,VStore,VPhysic, VName, VUnit, VType, VTypename, VLastCheck,
VIn, VOut, VStock, VCheck, VDiff)
values(@iSerial,@month,@store,@sPhysicno,@sName,@sUnit,@sTypeno,@sTypename,@dLast,@dIn,@dOut,@dStock,0,0)
if @@ERROR<>0 and @@error <> -1
begin
set @errcode= 0
goto Failure
end fetch next from cur_physic into @sPhysicno,@sName,@sUnit,@sTypeno,@sTypename
end close cur_physic
deallocate cur_physicif @ptype<>'' select * from VPHYSIC_MONTH where VTYPE=@ptype
else select * from VPHYSIC_MONTH
commit tran
return(1)Failure:
rollback tran t_inner
commit tran
return(@errcode)
end;
出现这种错误,可能是 你把数据源控件指向了它个人想法,只供参考