If else using case for returning value

I am trying to do an if else statement using CASE. If no records, then make it 0 then +1. Or else, just take the last record then +1. At first try i used ISNULL(statement,0). But it doesn’t have else statement.

Then I saw many other examples on StackOverflow for a case statement, but I don’t seem to understand how to implement it

INSERT INTO TICKET_SALES (
    TRXDATE, 
    KIOSKID, 
    BOOKINGREFERENCENUM, 
)
VALUES (
    '2019-01-18 16:59:29',
    'KIOSK1',
    ((SELECT TOP 1 BOOKINGREFERENCENUM FROM TICKET_SALES ORDER BY BOOKINGREFERENCENUM DESC)+1),
);

Answer

If you need to use expressions to supply values, you can change VALUES for SELECT, although you will only be able to supply 1 row with SELECT (unless you use UNION ALL).

INSERT INTO TICKET_SALES (
    TRXDATE, 
    KIOSKID, 
    BOOKINGREFERENCENUM
)
SELECT
    '2019-01-18 16:59:29',
    'KIOSK1',
    1 + ISNULL(
        (SELECT TOP 1 BOOKINGREFERENCENUM FROM TICKET_SALES ORDER BY BOOKINGREFERENCENUM DESC),
        0)

Please be ware of using this type of “tricks” to generate IDs, as others pointed out, it’s a very bad idea (check StepUp’s answer to know how to correct).