By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,304 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,304 IT Pros & Developers. It's quick & easy.

How to pass varchar parameter in stored procedure?

P: 45
Hi all,

After a long time i am back with a new problem. Right now I am having a problem with stored procedure.
I am using stored procedure with PHP/MYSQL. And evry time I am changing the condition of query in stored procedure. when I am passing numeric value then its working fine but when I am passing varchar or text in parameter then it ia not working. While I have declare variable as VARCHAR(255).
Here is SP query...

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE `testproc4`(IN `loc_name` VARCHAR(255), IN `property_country1` INT, IN `begin_beach_id` INT, IN `beach_id` INT, IN `lpi` INT, IN `lti` INT, OUT `prop_count` INT)
  2.     LANGUAGE SQL
  3.     NOT DETERMINISTIC
  4.     READS SQL DATA
  5.     SQL SECURITY INVOKER
  6.     COMMENT ''
  7. BEGIN 
  8.  
  9. IF property_country1 = "" THEN
  10. SELECT COUNT(pl.`property_id`) AS property_id INTO prop_count
  11. FROM `tbl_belt_propertylocation` AS pl
  12. JOIN `tbl_belt_propertydescription` AS pd ON (pl.property_id=pd.property_id)
  13. JOIN `tbl_belt_propertyfeatures` AS pf ON (pl.property_id=pf.property_id)
  14. WHERE `property_isdeleted` = 0 AND `property_beachdistance` > begin_beach_id AND `property_beachdistance`<= beach_id AND property_isdeleted=0 AND property_active=1; 
  15. ELSE
  16.  
  17. SELECT COUNT(pl.`property_id`) AS property_id INTO prop_count
  18. FROM `tbl_belt_propertylocation` AS pl
  19. JOIN `tbl_belt_propertydescription` AS pd ON (pl.property_id=pd.property_id)
  20. JOIN `tbl_belt_propertyfeatures` AS pf ON (pl.property_id=pf.property_id)
  21. JOIN `tbl_belt_location` AS country ON pl.property_country=country.id
  22. WHERE loc_name = property_country1 AND `property_beachdistance` > begin_beach_id AND `property_beachdistance`<= beach_id AND country.location_parent_id = lpi AND country.location_type_id = lti AND property_isdeleted=0 AND property_active=1; 
  23. END IF; 
  24. END
  25.  
Please help me out.

Thanks in advance
Sep 14 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.