Бесплатная идея:
а еще можно из хранимой процедуры слить данныее во временную таблицу
и прецепить ее через рекордсет.
на днях понадобилось получить в форму результат
сопоставления результатов нескольких хранимок в одной форме,
причем предполагается модификация алгоритма в этих хранимках
сделал так:
из каждой хранимки инсерт во временную таблицу
открываю рекордсетом join между временными таблицами.
назначаю рекордсет форме.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
Private Sub Form_Open(Cancel As Integer)
Dim IdPlaning
Dim prj
Dim OA
Dim SumRS As New ADODB.Recordset
If Not IsNull(Me.OpenArgs) Then
OA = Split(Me.OpenArgs, "<next>", , vbTextCompare)
IdPlaning = OA( 0 )
prj = OA( 1 )
Else
Cancel = True
Exit Sub
End If
Conn.Open (CurrentProject.connection.ConnectionString)
Conn.Execute "create table #tmp_grafik (dolid int , sort_order int , findepid int ,dolzhnost_name varchar(50), findep_name varchar(50), sumh int )"
Conn.Execute "create table #tmp_bg (dolid int , findepid int , sumh int )"
Conn.Execute "create table #tmp_all_records (dolid int , findepid int )"
Conn.Execute "insert into #tmp_bg (dolid , findepid , sumh ) select dolid , findep_id, sum(btime) from ts2.dbo.project_current_ver_time where btime <> 0 and prj ='" & prj & "' group by dolid , findep_id "
Conn.Execute "insert into #tmp_grafik (dolid, sort_order, findepid ,dolzhnost_name, findep_name, sumh ) exec dbo.pltime_by_dol " & IdPlaning
Conn.Execute "insert into #tmp_all_records (dolid, findepid ) select dolid, findepid from #tmp_bg union select dolid, findepid from #tmp_grafik "
RS.Open "select dolzhnost_name, namefingroup ,ar.* , (select sum(g.sumh) from #tmp_grafik g where g.dolid = ar.dolid and g.findepid = ar.findepid ) as sum_grafik ,(select sum(bg.sumh) from #tmp_bg bg where bg.dolid = ar.dolid and bg.findepid = ar.findepid ) as sum_bg from #tmp_all_records ar " & _
" left join dbo.kadr_dozhnost on ar.dolid = id_kadr_dolzhnost " & _
" left join dbo.finctrl_fingroup on ar.findepid =idfingruop order by findepid, sort_order", Conn, adOpenKeyset, adLockReadOnly
SumRS.Open "select (select sum(g.sumh) from #tmp_grafik g ) as sum_grafik ,(select sum(bg.sumh) from #tmp_bg bg ) as sum_bg ", Conn, adOpenStatic, adLockReadOnly
Me.Isum_grafik = SumRS!sum_grafik
Me.Isum_bg = SumRS!sum_bg
SumRS.Close
Set SumRS = Nothing
Do While RS.State And adStateFetching
DoEvents
Loop
RS.ActiveConnection = Nothing
Set Me.Recordset = RS
Conn.Close
Set Conn = Nothing
End Sub
