mysql use return value in same procedure

Question

my query execute and return a value and i need to use the return value in the same procedure, how to use it ?

my code is

  set @table_name_count = CONCAT('SELECT COUNT(*) into @xcount ', CONCAT( ' FROM ' ) , concat( table_name),concat( ' where issueNumber=',concat('30030'),concat( ' and ISSNcode='),CONCAT('112640402')));

     PREPARE statement from @table_name_count;
     EXECUTE statement;
     DEALLOCATE PREPARE statement;
     select 'hello4' from dual;
     IF xcount > 0 and xcount<>NULL THEN
       DELETE from table_name where issueNumber='30030' and ISSNcode='112640402' ;
     END IF  ;

here i need to use the @xcount in the conditions

IF xcount > 0 and xcount<>NULL THEN
   DELETE from table_name where issueNumber='30030' and ISSNcode='112640402' ;
 END IF  ;

peterm · Accepted Answer · 2014-01-10 07:35:29Z

1

You just forgot @ before the variable name since you're dealing with user(session) variables. Your IF statement should like this

IF @xcount > 0 THEN
   ^

You don't need to check for NULL since COUNT() always returns a value.

Here is SQLFiddle demo

Now there is no point in making such a check for record(s) existence before issuing DELETE. If there are no rows matching conditions in WHERE clause of DELETE statement it just won't do anything and won't produce any error.

Therefore you can ditch all the code except for DELETE statement.

edited Jan 10, 2014 at 7:35

answered Jan 10, 2014 at 7:27

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql use return value in same procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related