使用存储过程的输出参数. 如你的存储过程: create proc 存储过程 @rc bigint output as select * into a from c select * into b from c select * from c set @rc=@@rowcount //使用系统函数@@rowcount可以得到最后一句语句影响的记录数量,并赋值给输----出参数@rc go--然后在vb调用该存储过程并得到存储过程执行的输出参数值即可.
SET NOCOUNT ON SELECT ... INTO ... SELECT ... INTO ... SET NOCOUNT OFF SELECT ... FROM ...
SET NOCOUNT ON SELECT ... INTO ... SELECT ... INTO ... SET NOCOUNT OFF SELECT ... FROM ...这样也不行啊
我的存储过程是这样的: alter proc many_first_total(@nian int,@yue int,@qry_str varchar(50)) as set nocount on begin begin tran if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tab1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tab1]declare @in decimal(15,4),@out decimal(15,4),@bili_out decimal(15,4),@matnr varchar(18) exec ('select a.*,b.tax into tab1 from view_details a left join tax b on a.nian=b.nian and a.yue=b.yue and a.matnr=b.matnr and a.lifnr=b.lifnr where a.nian=' + @nian + ' and a.yue=' + @yue+@qry_str)update tab1 set tax=0 where tax is nulldeclare cur_matnr cursor for select distinct matnr from tab1 where get_typ='X' --内外制的物料open cur_matnr fetch next from cur_matnr into @matnr while (@@fetch_status<>-1) begin select @in=0 select @out=0 select @in=in0,@out=out0 from in_out where matnr=@matnr and nian=@nian and yue=@yue if (@in + @out)=0 or @in is null or @out is null select @bili_out=0 else select @bili_out=@out/(@in+@out) update tab1 set bili=@bili_out where matnr= @matnr update tab1 set bili=(1-@bili_out) where up_matnr=@matnr fetch next from cur_matnr into @matnr endupdate tab1 set bili=1 where bili is null update tab1 set quota=0 where quota is null update tab1 set menge=0 where menge is null update tab1 set fore1=0 where fore1 is null update tab1 set fore2=0 where fore2 is null update tab1 set fore3=0 where fore3 is null --select * from tab1 select fert_m,zkgrp,series,sum(stprs*quota*menge*(1+tax)*bili/100) as stprs,sum(nztpr*quota*menge*(1+tax)*bili/100) as nztpr,sum(fore1*quota*menge*(1+tax)*bili/100) as fore1, sum(fore2*quota*menge*(1+tax)*bili/100) as fore2,sum(fore3*quota*menge*(1+tax)*bili/100) as fore3 into #tab2 from tab1 group by fert_m,zkgrp,series order by series,fert_mdrop table tab1 close cur_matnr deallocate cur_matnr set nocount off select * from #tab2 --select @count=@@rowcount if @@error !=0 begin rollback tran return end commit tran end 我要的是#tab2中的记录 ,在查询分析器中执行时返回2条记录,但返回给VB的recordset后,其recordcount属性是32
//这个是第一个"select into"语句执行影响记录数量
如你的存储过程:
create proc 存储过程
@rc bigint output
as
select * into a from c
select * into b from c
select * from c
set @rc=@@rowcount //使用系统函数@@rowcount可以得到最后一句语句影响的记录数量,并赋值给输----出参数@rc
go--然后在vb调用该存储过程并得到存储过程执行的输出参数值即可.
SELECT ... INTO ...
SELECT ... INTO ...
SET NOCOUNT OFF
SELECT ... FROM ...
SET NOCOUNT ON
SELECT ... INTO ...
SELECT ... INTO ...
SET NOCOUNT OFF
SELECT ... FROM ...这样也不行啊
alter proc many_first_total(@nian int,@yue int,@qry_str varchar(50))
as
set nocount on
begin
begin tran
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tab1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tab1]declare @in decimal(15,4),@out decimal(15,4),@bili_out decimal(15,4),@matnr varchar(18)
exec ('select a.*,b.tax into tab1 from view_details a left join tax b on a.nian=b.nian and a.yue=b.yue
and a.matnr=b.matnr and a.lifnr=b.lifnr where a.nian=' + @nian + ' and a.yue=' + @yue+@qry_str)update tab1 set tax=0 where tax is nulldeclare cur_matnr cursor for
select distinct matnr from tab1 where get_typ='X' --内外制的物料open cur_matnr
fetch next from cur_matnr into @matnr
while (@@fetch_status<>-1)
begin
select @in=0
select @out=0
select @in=in0,@out=out0 from in_out where matnr=@matnr and nian=@nian and yue=@yue
if (@in + @out)=0 or @in is null or @out is null
select @bili_out=0
else
select @bili_out=@out/(@in+@out)
update tab1 set bili=@bili_out where matnr= @matnr
update tab1 set bili=(1-@bili_out) where up_matnr=@matnr
fetch next from cur_matnr into @matnr
endupdate tab1 set bili=1 where bili is null
update tab1 set quota=0 where quota is null
update tab1 set menge=0 where menge is null
update tab1 set fore1=0 where fore1 is null
update tab1 set fore2=0 where fore2 is null
update tab1 set fore3=0 where fore3 is null
--select * from tab1
select fert_m,zkgrp,series,sum(stprs*quota*menge*(1+tax)*bili/100) as stprs,sum(nztpr*quota*menge*(1+tax)*bili/100) as nztpr,sum(fore1*quota*menge*(1+tax)*bili/100) as fore1,
sum(fore2*quota*menge*(1+tax)*bili/100) as fore2,sum(fore3*quota*menge*(1+tax)*bili/100) as fore3 into #tab2 from tab1 group by fert_m,zkgrp,series order by series,fert_mdrop table tab1
close cur_matnr
deallocate cur_matnr
set nocount off
select * from #tab2 --select @count=@@rowcount
if @@error !=0
begin
rollback tran
return
end
commit tran
end
我要的是#tab2中的记录 ,在查询分析器中执行时返回2条记录,但返回给VB的recordset后,其recordcount属性是32