Stored procedure isn't returning any results

Question

0.00/5 (No votes)

See more:

Hello Experts,

I am Query in which there should be a loop for each row of the results from the Query to pass a parameter to external Proc. Here it is without any error.

BEGIN 

DECLARE v_BIN CHAR(10);
DECLARE RESULT INTEGER DEFAULT 0 ; 

FOR v_row AS 

    SELECT T2 . ICITEM , T2 . BABLOC , 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
    
    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	 
 
    DO	 
	 	 
        SET v_BIN = v_row.BABLOC;	                                               
   	call TESTDATA.PARECR24SP (  '01' , '13  ',v_BIN ,RESULT) ;                               
END FOR;

END;

Now I want the results to be printed/ shown in output window. Here it says just success message.

This is really a big thing now for me. I banging my head to the wall since last three days. please help

Posted 30-Sep-15 4:07am

sudevsu

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-09-30T04:44:00

The procedure you've created is designed to loop through the rows and call the sub program. If you want to return a result set you need to design it differently:
- use DYNAMIC RESULT SETS
- define a cursor
- open a cursor
- return

Have a look at the procedure definition in Example 1: Calling a stored procedure that returns a single result set[^]

ADDITION

What comes to the question if the query is correct or not I really can't say since I know nothing about the tables nor the logic.

If it would have syntactical problems they would show as errors but if it has logical errors and returns wrong rows or no rows at all, that's up to the statement logic.

Since you need to return the result to the calling side, have a test run with the following. That's part of your original query so it should return something meaningful.

One thing I noticed is that you were missing the keyword CURSOR...

SQL

CREATE PROCEDURE ResultTest () LANGUAGE SQL
 DYNAMIC RESULT SETS 1
 BEGIN
    DECLARE v_row CURSOR FOR 
    SELECT A.BACMP, 
           A.BALOC, 
           A.BAITEM,    
           A.BABLOC,
           A.BAQOH, 
           A.BAQCM
   FROM TESTDATA.VINBINI A 
   WHERE A.BALOC = '13' 
   AND   1 = A.BACMP 
   AND   A.BAQOH - A.BAQCM > 0;

    OPEN v_row;

    RETURN;
END