Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    Unanswered: store procedure error- parameter mismatched

    my sp is
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `zero`.`sp_for_insert_into_account_db`$$
    CREATE PROCEDURE `zero`.`sp_for_insert_into_account_db` (usr_key char(6),usr_name varchar(15),usr_password varchar(15),OUT output_message INT)
    BEGIN
    DECLARE no_of_row INT;

    SELECT COUNT(*) INTO no_of_row from account_db;

    IF no_of_row < 4 THEN

    SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (',usr_key,usr_name,usr_password,')');

    PREPARE stmt FROM @s;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

    SET output_message=1;

    ELSE

    SET output_message=0;



    END IF;
    END$$

    DELIMITER ;

    i m calling it with query
    call sp_for_insert_into_account_db('a','b','c',@output_ message);
    i think this is correct... still it is showing error like
    coloum count does not match value... i m passing 4 argument.....
    pls tell me why this error is occour ..........

    i already check with this syntax (by default parameter is IN type)
    `sp_for_insert_into_account_db`(IN usr_key char(6),IN usr_name varchar(15), IN usr_password varchar(15),OUT output_message INT)
    Last edited by rohitamitpathak; 03-11-11 at 11:29.
    Amit Pathak
    S/w Developer
    Zero Systems Pvt Ltd..

  2. #2
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    I got the solution...

    Quote Originally Posted by rohitamitpathak View Post
    my sp is
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `zero`.`sp_for_insert_into_account_db`$$
    CREATE PROCEDURE `zero`.`sp_for_insert_into_account_db` (usr_key char(6),usr_name varchar(15),usr_password varchar(15),OUT output_message INT)
    BEGIN
    DECLARE no_of_row INT;

    SELECT COUNT(*) INTO no_of_row from account_db;

    IF no_of_row < 4 THEN

    SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (',usr_key,usr_name,usr_password,')');

    PREPARE stmt FROM @s;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

    SET output_message=1;

    ELSE

    SET output_message=0;



    END IF;
    END$$

    DELIMITER ;

    i m calling it with query
    call sp_for_insert_into_account_db('a','b','c',@output_ message);
    i think this is correct... still it is showing error like
    coloum count does not match value... i m passing 4 argument.....
    pls tell me why this error is occour ..........

    i already check with this syntax (by default parameter is IN type)
    `sp_for_insert_into_account_db`(IN usr_key char(6),IN usr_name varchar(15), IN usr_password varchar(15),OUT output_message INT)
    Basically in this scenario there are no need to use prepared statement, i was using this with wrong syntax-
    BEFORE:-

    SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (',usr_key,usr_name,usr_password,')');

    WRITE SYNTAX IS-

    SET @s = CONCAT('insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (\'',usr_key,'\',\'',usr_name,'\',\'',usr_password ,'\')');
    You can also do this without prepared statement, for this use

    insert into account_db (USR_KEY,USR_NAME,USR_PWD) VALUES (usr_key,usr_name,usr_password) ;
    Amit Pathak
    S/w Developer
    Zero Systems Pvt Ltd..

Tags for this Thread

Posting Permissions

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