CODEWITHSUNDEEP
mysql
user1703145
user1703145

Reputation: 175

mysql use return value in same procedure

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  ;

Upvotes: 1

Views: 48

Answers (1)

peterm
peterm

Reputation: 92785

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.

Upvotes: 1

Related Questions