对于存储过程的每一行结果集，请使用Resultset中的参数调用外部过程 [英] For Each row of resultset from a stored procedure call external Procedure with parameters as from Resultset

问题描述

Hello Experts，



我有一个存储过程，它在VB.NET中为Grid显示返回一个数据集。

现在要求有点难度。

我还有一个存储过程应该在返回的数据集的每一行上调用。



我想知道我们怎么做数据库而不是VB.NET。因为有很多行所以我不认为在Grid的每一行上调用SP是一个好习惯。



这是我的存储过程1.

Hello Experts,

I have a Stored procedure that returns a dataset in VB.NET for a Grid display.
Now the requirement is little tough.
I have got one more stored procedure that should be called on each row of the returned Dataset.

I wanted to know how can we do it database instead of VB.NET. Because there are lot of rows so I don't think its a good practice to call SP on each row of Grid.

Here is my stored procedure 1.

DECLARE CR1 CURSOR WITH RETURN FOR
SELECT T2 . ICITEM , T2 . BABLOC as binloc, C.ISSIZE , D . UNITSPER
, E . NBRRCKBINB , F . ICDSC1 , F . ICDSC2 , T2 . TQOH , T2 . TQCM
, CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER )
NBR_USED_RACKS
, E . NBRRCKBINB - ( CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER ) )
NBR_OPEN_RACKS , ( E . NBRRCKBINB * D . UNITSPER ) - T2 . TQOH
FROM (
SELECT A . BACMP , A . BALOC , MIN ( A . BAITEM ) ICITEM
, A . BABLOC , INTEGER ( SUM ( A . BAQOH ) ) TQOH ,
INTEGER ( SUM ( A . BAQCM ) ) TQCM
FROM TESTDATA . VINBINI A WHERE A . BALOC = '13' AND 1 = A . BACMP AND
A . BAQOH - A . BAQCM > 0 GROUP BY A . BACMP , A . BALOC , A . BABLOC HAVING
MIN ( A . BAITEM ) = MAX ( A . BAITEM )
AND SUM ( A . BAQOH - A . BAQCM ) > 0
) AS T2
, TESTDATA . PALITMLOC B , TESTDATA . VINITEMSIZ C , TESTDATA . PALSIZQTY D , TESTDATA . PALBINPF E
, TESTDATA . VINITEM F
WHERE T2 . BACMP = B . TACOMP AND T2 . ICITEM = B . ICITEM
AND T2 . BALOC = B . IALOC AND T2 . ICITEM = F . ICITEM
AND T2 . ICITEM = C . ISITEM
AND B . PALLETID = D . PALLETID
AND C . ISSIZE = D . ISSIZE
AND E . TACOMP = T2 . BACMP
AND E . IALOC = T2 . BALOC
AND E . IMBLOC = T2 . BABLOC
AND E . PALLETID = B . PALLETID
ORDER BY 1 , 2 ;

现在开启这个SP的每一行，我应该调用另一个程序

Now on each row of this SP, I should call another procedure

SP_PARECR24SP('01','13','binloc')

这里来自存储过程1的bin位置应该是Storedprocedure 2的参数。



不幸的是，通常做这个数据库的人/后期工作是长假，我不想说我没有取代他。所以我一直在努力解决这个问题。



非常感谢您的帮助。

Here bin location from stored procedure 1 should be a in parameter for Storedprocedure 2.

unfortunately , the guy who usually does all this Database / back end work is on long vacation and I don't want to say that I am failed in replacing him. So I have been struggling on this so badly.

Your help is much appreciated.

推荐答案

实际上，在每一行上调用sp都会导致性能下降肯定会降级。

更好地改变方法而不是用sp。

actually ,calling sp on each single row will cause performance degrade surely.
better to change the approach instead of going with sp.

这篇关于对于存储过程的每一行结果集，请使用Resultset中的参数调用外部过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！