Mysql – calling thesql procedure return empty result

MySQLmysql-5.5mysql-5.6mysql-5.7mysql-workbench

when i call procedure it gives me empty result

   call hussian('prstate','table_1','column1,column2,column3',1)

it run but gives empty result, please help me…

 DELIMITER // 

 CREATE PROCEDURE hussain(IN src_col varchar(20), 
                     IN tblname varchar(100), 
                     IN col_names varchar(100), 
                     IN _leadid int)

 BEGIN
    IF (col_names  = 'column1' AND col_names  = 'column2'  AND col_names = 'column3')
THEN

    SET @sql = CONCAT('insert into ', tblname, '(',col_names,')', ' select ' ,src_col,',','firstname,lastname from tbllead where id = ',_leadid); 
    PREPARE s1 from @sql;
     EXECUTE s1;
    DEALLOCATE PREPARE s1;
 END IF;
  END //
  DELIMITER ;

it insert data into table when i romove if condition…..

Best Answer

Basically

'column1' 'column2' 'column3' is different than 'column1,column2,column3'

So, you must compare the whole string:

 BEGIN
IF (col_names  = 'column1,column2,column3')
THEN
.
.
.

If you want to compare each variable separately, then you must pass them separate in your procedure call:

Call:

call hussian('prstate','table_1','column1','column2','column3',1)

Procedure:

CREATE PROCEDURE hussain(IN src_col varchar(20), 
                         IN tblname varchar(100), 
                         IN col_names1 varchar(100), 
                         IN col_names2 varchar(100), 
                         IN col_names3 varchar(100), 
                         IN _leadid int)

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

MySQL overhead of calling a Stored Procedure

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons

So there are good reasons to use prepared statements:

Save on query parsing

Save on data conversion and copying

Avoid SQL Injection

Save memory on handling blobs

There are also drawbacks and chewats of using prepared statements:

Query cache does not work

Extra server round trip required if statement used only once

Not all statements can be prepared. So you can’t use prepared API exclusively you’ll need to fall back to normal API for some statements

Newer and sometimes buggy code. I had a lot of problems with PHP prepared statements. It is getting better but still it is less mature than standard API

You can’t use placeholders in place of all identifiers. For example you can’t use them for table name. In certain version it even does not work for LIMIT boundaries

Inconvenient list handling. Unlike in for example PEAR emulated prepared statements there is no nice way to pass list of values to IN

Harder tracing. Logs were now fixed to include full statement text not only “Execute” but in SHOW INNODB STATUS you would still see statements without actual values – quite inconvenient for analyses

Since what you are experiencing is the expected behavior, you should not use Prepare Statements. You are making many calls to execute a single command. If you simply ran this

stmt = conn.createStatement();
rs = stmt.executeQuery("call sp_one('a','b')");

the complete parsing of the SQL, preparing for execution, and the parameters already being passed in through the SQL statement are all done on the server side with one call.

Please read that Percona blog for more details.

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

MySQL overhead of calling a Stored Procedure

Related Question