Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Output Parameter Problem

    Hi,
    I am calling Stored Procedure from PHP and i am running out with errors. So i changed OUT to IN Parameter and SET _Status to SELECT 'Role Already Exists' but am not getting those results
    Here is my code,
    Code:
    DELIMITER $$
    CREATE PROCEDURE SP_Register_Role(
        IN _Role_Name VARCHAR(10),
        OUT _Status VARCHAR(30))
     BEGIN
        DECLARE CheckExists int;
        SET CheckExists = 0;
        SELECT COUNT(*) INTO CheckExists FROM Role_Master WHERE Role_Name = _Role_Name;
        IF CheckExists > 0 THEN
            SET _Status = 'Role Already Exists';
        ELSE
            INSERT INTO Role_Master(Role_Name) VALUES(_Role_Name);
            SET _Status =  'Role Created';
        END IF;
    END$$
    Code:
    $dbh = PHP_DB_Connect();
    		$Qry = $dbh->prepare("CALL SP_Register_Role(?,?)");
    		$Qry->bindParam(1,$_POST["RoleName"],PDO::PARAM_STR,10);
    		$Qry->bindParam(2,$SP_Return_Value,PDO::PARAM_STR|PDO::PARAM_INT_OUT,30);
    		$Result = $Qry->execute();
    		if(!$Result){
    			  $arr = $Qry->errorInfo();
    			  print_r($arr);
    		}else{
    			print $SP_Return_Value;
    		}

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you even bothering to use a stored procedure here?

    there are two options to the INSERT statement that you could be using -- IGNORE, or, alternatively, ON DUPLICATE KEY UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Hi thankyou,
    Are you telling not to use stored procedure here? do i need to handle this with query alone ?
    any examples ?

  4. #4
    Join Date
    Nov 2008
    Posts
    117
    Hi thankyou,
    Are you telling not to use stored procedure here? do i need to handle this with query alone ?
    any examples ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •