我在报表(主从报表)要用到存储过程,比如主表select mainid , from maintbl
从表是存储过程 比如 create sp_test @mainid integer as select mainid ,subid from subtbl where mainid=@mainid 当然实际上从表的存储过程是比较复杂的,我想知道如何把主表的mainid作为参数传递到从表的存储过程中,谢谢了
从表是存储过程 比如 create sp_test @mainid integer as select mainid ,subid from subtbl where mainid=@mainid 当然实际上从表的存储过程是比较复杂的,我想知道如何把主表的mainid作为参数传递到从表的存储过程中,谢谢了
把查询出来的mainid 存入的从表的一个变量中,
再传进存储过程的参数@BB...
CREATE PROCEDURE usp_test AS
declare @mainid char(20)
declare @count int
create table #temp1(id int,num int)
insert into #temp1
select mainid from maintbl
declare read_userid cursor for select mainid ,subid from subtbl
select @count=count(id) from subtbl
if @count>0
begin
open read_userid
----------------------------------------------------------------------------------------------------------------------------while begin
while @count>0
begin
fetch next from read_userid into @mainid
--读取数量金额表
if exists( select * from subtbl where id=@mainid )
begin
--希望地操作
end
set @count=@count-1
end
---------------------------------------------------------------------------------------------------------------------------while end
commit transaction
end
deallocate read_userid
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------定义游标endGO
CREATE PROCEDURE usp_test AS
declare @mainid char(20)
declare @count int
create table #temp1(id int)
insert into #temp1
select mainid from maintbl
declare read_userid cursor for select mainid ,subid from subtbl
select @count=count(id) from subtbl
if @count>0
begin
open read_userid
while @count>0
begin
fetch next from read_userid into @mainid
if exists( select * from subtbl where id=@mainid )
begin
--希望地操作
end
set @count=@count-1
end
commit transaction
end
deallocate read_userid
GO
insert into #temp1
select mainid from maintbl 实际上只有一个mainid转递过来,但是没有办法传递
insert into #temp1
select mainid,0 from maintbl
你这样如果还不能取得你想要的mainID,那你要看看你的main表中到底有几个适合的mainID了