0

Im trying to run the following stored procedure, however it is not giving any output.. If i select the values out without using the OUT parameters it works fine..

SELECT PWORD, REPLYATTR; -- This works fine

But doing it 'correctly' doesn't for some reason, any ideas guys?

DROP PROCEDURE `uuu`//

CREATE DEFINER=`auth_tracker`@`%` PROCEDURE `uuu`( IN USERNAME varchar(100), 
                                                   OUT REPLYATTR varchar(100),
                                                   OUT PWORD varchar(100) )

BEGIN

DECLARE USER_PROD_ID INTEGER; 
DECLARE ATTR VARCHAR(100); 
DECLARE VAL VARCHAR(100); 
DECLARE no_more_rows BOOLEAN; -- Loop exit condition; set by NOT FOUND handler

DECLARE PoolHint VARCHAR(100) DEFAULT NULL;
DECLARE FramedIP VARCHAR(100) DEFAULT NULL; 
DECLARE FramedRoute VARCHAR(100) DEFAULT NULL;  

-- DECLARE PWORD VARCHAR(100) DEFAULT NULL;
-- DECLARE REPLYATTR VARCHAR(100);


DECLARE cur1 CURSOR 
    FOR 
 SELECT attribute_name, value
   FROM user_product_attribute upa, product_attribute pa
  WHERE upa.product_attribute_id = pa.product_attribute_id
    AND upa.user_product_id = USER_PROD_ID;

-- Declare handler to set loop exit condition
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

-- Main logic 
SELECT upa.user_product_id
  INTO USER_PROD_ID
  FROM user_product_attribute upa, user_product up, product_attribute pa, product p
 WHERE pa.attribute_name = 'username'
   AND pa.product_attribute_id = upa.product_attribute_id
   AND pa.product_id  = p.product_id

   AND up.status      = 'active'
   AND p.product_name = 'broadband'
   AND upa.value      = USERNAME

 LIMIT 1;

  -- Open the cursor
  OPEN cur1;

  -- Start Looping
  the_loop: LOOP

  -- Get Attrib + value
  FETCH cur1
    INTO ATTR, VAL;

  -- Exit if no rows
  IF no_more_rows THEN
    CLOSE cur1;
    LEAVE the_loop;
  END IF;

  -- Grab certain vars
  IF ATTR = 'poolhint' THEN SET PoolHint := VAL; END IF;
  IF ATTR = 'Framed-IP-Address' THEN SET FramedIP := VAL; END IF;
  IF ATTR = 'Framed-Route' THEN SET FramedRoute := VAL; END IF;
  IF ATTR = 'password' THEN SET PWORD := VAL; END IF;

  -- End Loop    
  END LOOP the_loop;

-- Concat 
IF (FramedIP IS NOT NULL) THEN SET REPLYATTR := CONCAT('Framed-IP-Address=',FramedIP);
ELSEIF (PoolHint IS NOT NULL) THEN SET REPLYATTR := CONCAT('PoolHint=',PoolHint);  
END IF;

-- Add Route
IF (FramedIP IS NOT NULL AND FramedRoute IS NOT NULL) 
THEN SET REPLYATTR := CONCAT(REPLYATTR,',Framed-Route="',FramedRoute,'"');
END IF;

END//

1 Answer 1

1

How are you calling the procedure?

If you pass user-defined variables in when you call the proc, then you should get the output you want.

Example:

CALL uuu('bobby',@replyattr,@pname);
SELECT @replyattr,@pname;
Sign up to request clarification or add additional context in comments.

1 Comment

Ok i feel stupid now... I wasnt selecting the values after calling the proc.. Thanks for the help..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.