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 ;
Now on each row of this SP, I should call another procedure
SP_PARECR24SP('01','13','binloc')
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.