Store procedure result Mysql

[mangel.murti]

I have created a sp little bit confuse about it. how to count result set if data availabe then show it and if not then retun ResultStatus is zero. did not work much with sp. thanks for any help suggestions.

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_GetProduct`(IN `iProductCategoryId` INT)
BEGIN
  DECLARE ResultStatus INT default 0;
  DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
    #set ErrorCode = -999;
    rollback;
  END;

  DECLARE exit handler for sqlwarning
  BEGIN
    -- WARNING
    #set ErrorCode = -888;
    rollback;
  END; 

  START TRANSACTION;
   SELECT P.iProductId AS ProductId,P.vProductName AS ProductName,P.vProductCode AS ProductCode,P.iCategoryId AS CategoryId,P.tDescription AS Description,P.vProductWebsiteURL AS ProductWebsiteURL,P.iProductCategoryId AS CategoryId,P.eShowOnHomePage AS ShowOnHomePage,P.vProductImage1 AS ProductImage1,P.vProductImage2 AS ProductImage2,P.dPrice AS Price,P.eStatus AS Status,P.iCreatedById AS CreatedById,P.dCreatedDate AS CreatedDate,P.iModifiedById AS ModifiedById,P.dModifiedDate AS ModifiedDate, PC.vProductCategoryName AS ProductCategoryName FROM skin_product P
    INNER JOIN skin_productcategory PC ON PC.iProductCategoryId = P.iProductCategoryId
    WHERE P.iProductCategoryId = IFNULL(iProductCategoryId, P.iProductCategoryId); 
    #set ErrorCode = 0;
   SET ResultStatus=1;
   SELECT @ResultStatus as Status;
  COMMIT;
  SELECT @ResultStatus as Status;
END

[ryantroop]

ehhh...

First, Im not sure why you are wrapping a select in a transaction. May just be my not following your logic, or maybe me being naive, but Im pretty sure transactions are meant to make sure everything went smoothly on inserts or updates before committing changes. You are doing neither of those things, and therefore do not need a transaction.

In MySQL, the @ symbol represents a user defined variable - which means it is actually accessable outside the stored procedure as long as the session remains open. Do not use them unless you know why you are using them.

What your proc is doing is this ->

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_GetProduct`(IN `iProductCategoryId` INT)
--create this procedure. it will take in 1 int variable called iProductCategoryId
--note, if the procedure already exists you will have to delete it before saving
BEGIN
  --start the procedure
  DECLARE ResultStatus INT default 0; --make a variable for an int. default 0 does nothing if I remember correctly. If not ignore my correction
  --here, give your default.
  set ResultStatus = 0;
  DECLARE ErrorCode INT;
  set ErrorCode = 0; -- because you never declare it, it likely wasnt working...
  DECLARE exit handler for sqlexception  --take care of SQL error
  BEGIN
    -- ERROR
    #set ErrorCode = -999;
    rollback; -- totally not necessary
  END;

  DECLARE exit handler for sqlwarning
  BEGIN
    -- WARNING
    #set ErrorCode = -888; 
    rollback; --totally not necessary
  END; 

  START TRANSACTION; --not necessary
   SELECT --just a select, result set 1
     P.iProductId AS ProductId,
     P.vProductName AS ProductName,
     P.vProductCode AS ProductCode,
     P.iCategoryId AS CategoryId,
     P.tDescription AS Description,
     P.vProductWebsiteURL AS ProductWebsiteURL,
     P.iProductCategoryId AS CategoryId,
     P.eShowOnHomePage AS ShowOnHomePage,
     P.vProductImage1 AS ProductImage1,
     P.vProductImage2 AS ProductImage2,
     P.dPrice AS Price,
     P.eStatus AS Status,
     P.iCreatedById AS CreatedById,
     P.dCreatedDate AS CreatedDate,
     P.iModifiedById AS ModifiedById,
     P.dModifiedDate AS ModifiedDate, 
     PC.vProductCategoryName AS ProductCategoryName 
    FROM 
     skin_product P
      INNER JOIN skin_productcategory PC ON PC.iProductCategoryId = P.iProductCategoryId
    WHERE 
      P.iProductCategoryId = IFNULL(iProductCategoryId, P.iProductCategoryId); 

   #set ErrorCode = 0; --why?

   SET ResultStatus = 1;

   SELECT 
     @ResultStatus as Status; --just a select, result set 2. However, you are using a User Defiend Var, @ResultStatus as opposed to the DECLAREd variable ResultStatus, so this is null.
  COMMIT; -- not necessary
  SELECT 
    @ResultStatus as Status; --just a select, result set 3. Same issue as above.
END --no ending ";" or "//", so this probably wont compile, or you will leave your session in a strange cursor state.

So, you have a lot of problems in this little snippet, and a lot that doesn't need to be done, in my opinion.

Start there, and figure out if you are actually doing what you think you are.

[mangel.murti]

Thanks a lot for your explanation. i am doing some thing worng there that why ask here ... you cleared couple of things to me. i have one more thing to ask , when select query execute then how put condition like if result set is empty SP return Status 0 and other wise return result?

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_GetProduct`(IN `iProductCategoryId` INT)
BEGIN
   DECLARE ResultStatus INT;
   SELECT * from sometable;
   // i want here a conditon that if above query return empty resultset then SP return 0 other wise resultset will return;
   // so i put like below but not sure is this correct? 
   SET @ResultStatus = FOUND_ROWS();
   if(@ResultStatus > 0) THEN
       // here all result will be return     
   ELSE
        // nothing found return 0;
        SELECT @ResultStatus as Status; 
   END IF;
END

[ryantroop]

You can do that in two ways -- in PHP you will know it's empty because your result set will be empty. That's probably the simplest and most straight forward way. You can use something like mysql_num_rows.

To do it with pure SQL, you can do aggregate functions like Count() to get the number of results in the set (but if nothing comes back, you're still stuck with an empty set). You can use something like

SELECT FOUND_ROWS();

and that will return the count for you (even if it's 0). However, you will then be writing code that expects that result set on top of the result set you just returned, which is rediculous. FOUND_ROWS() is more for procs that fill temp tables and does some sort of data massaging (in my opinion).