MySQL Bugs: #7913: suggestion: allow flow control logic in batch sql

Bug #7913	suggestion: allow flow control logic in batch sql
Submitted:	14 Jan 2005 17:10	Modified:	29 Sep 2007 11:20
Reporter:	Matthew Murphy	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	4.1x and below	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When writing a batch sql script containing a transaction, it does not appear to be possible to do a test at the end to determine whether the transaction should be committed or rolled back.  It would be nice to be able to do this and other simple flow control in a batch sql script.

For example:

SET @orderid = 33;

SET AUTOCOMMIT=0;

START TRANSACTION;

... some code that sorts orders into big and small orders tables... for example.... 
 
IF (SELECT sum(price) FROM orders) = (SELECT sum(price) FROM bigorders) + (SELECT sum(price) FROM smallorders)
   BEGIN
      COMMIT
   END
ELSE
   BEGIN
     ROLLBACK
   END

How to repeat:
The SELECT IF syntax and the SELECT CASE syntax both don't allow a statement like COMMIT or ROLLBACK to be used as an argument.  

The Stored Procedure functionality in mysql 5 does allow for this, but I think it would be useful to include this kind of flow control construct in mysql 4.1x

Suggested fix:
Allow IF THEN ELSE flow control to result in a COMMIT or a ROLLBACK being called, so that batch mysql files can be self-contained enough to decide on their own if a transaction should be committed or rolled back. This would seem to entail porting the flow control logic from mysql 5 into 4.1x...

Sorry, but features like this will surely not be implemented in versions 4.x.y.

You can do it this way:

select @cond:=1;
select @t1:=if(@cond=1,'
CREATE TABLE  example (
  `Field1` int(11) NOT NULL,
  `Field2` varchar(32) collate latin1_spanish_ci default NULL,
  `Field3` int(11) default NULL,
  PRIMARY KEY  (`Field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
','select 1');

prepare stmt from @t1;
execute stmt;
drop prepare stmt;