Is there a ternary operator in MySQL stored procedures?

Question

Does SQL (MySQL) support ternary operators?

I have this SQL:

CREATE TEMPORARY TABLE SalesTbl (
    CTR int primary key auto_increment, 
    VRNOA VARCHAR(50), 
    DESCRIPTION VARCHAR(50), 
    INQTY INT, 
    OUTQTY INT, 
    ETYPE VARCHAR(50), 
    RTotal DECIMAL(19,2)
);

INSERT INTO SalesTbl
SELECT 0, stockmain.VRNOA AS 'VRNOA', Item.description as 'DESCRIPTION', 
       qty as 'QTY', Stockmain.etype 'ETYPE', null 
FROM StockMain 
     INNER JOIN StockDetail on StockMain.stid = StockDetail.stid 
     INNER JOIN ITEM on StockDetail.item_id = Item.item_id 
WHERE StockDetail.item_id = 6 
AND   StockMain.vrdate BETWEEN '2010/10/02' AND '2013/12/02' 
ORDER BY Qty;

Would it be possible to do something like this in MySQL?

(QTY < 0) ? QTY : 0

If QTY is less than 0, then use QTY, otherwise use 0.

Answer 1

You need the CASE statement (alternatively there are IF statements but CASE is the standard across many databases) which returns the expression of the first true criteria.
http://dev.mysql.com/doc/refman/5.0/en/case.html

CREATE TEMPORARY TABLE SalesTbl (CTR int primary key auto_increment, 
                                 VRNOA VARCHAR(50), 
                                 DESCRIPTION VARCHAR(50), 
                                 INQTY INT, 
                                 OUTQTY INT, 
                                 ETYPE VARCHAR(50), 
                                 RTotal DECIMAL(19,2));

INSERT INTO SalesTbl

SELECT 
  0, 
  stockmain.VRNOA AS 'VRNOA', 
  Item.description as 'DESCRIPTION', 
  qty as 'QTY', 
  CASE WHEN qty<0 then qty end as InQTY, 
  CASE WHEN qty>0 then qty end as OutQTY,
  Stockmain.etype 'ETYPE', 
  null 
 FROM StockMain 
INNER JOIN StockDetail on StockMain.stid = StockDetail.stid 
INNER JOIN ITEM on StockDetail.item_id = Item.item_id 
WHERE StockDetail.item_id = 6 
AND StockMain.vrdate BETWEEN '2010/10/02' AND '2013/12/02' 
ORDER BY Qty;

Answer 2

There is no dedicated ternary operator in MySQL but you can get something close to it like this:

delimiter //
DROP PROCEDURE IF EXISTS `foobar`//
CREATE PROCEDURE `foobar`()
BEGIN
  declare romulan int default 0;
  set romulan = 1;

  select if (romulan = 1, 52, 99) into romulan;

  select romulan;
END//

Run it like this:

mysql> call foobar(3);

It prints:

+---------+
| romulan |
+---------+
|      52 |
+---------+
1 row in set (0.00 sec)

So if romulan is 1, then romulan becomes 52, otherwise romulan gets 99.