MySQL: Is it possible to set a variable outside of…

WHAT
I’m on MySQL 5.6 and my account only has permissions for SELECT and USAGE (as found using the SHOW GRANTS command).

Presumably because of this using DECLARE before SELECT returns a generic syntax error.

WHY
I want to use variables to both make the code easier to read and maintain. Below you can see variable @isOwn is needed in WHERE clause and the day differences could be shortened (note: DATEDIFF didn’t work for me in WHERE for some reason)

QUESTION
Being able to declare the variables before SELECT would simplify everything. Am I doing something wrong or can I not do this because of my minimal permissions?

CODE EXAMPLE

DECLARE @test INT = 5 -- <<< syntax error

SELECT
    CASE
        WHEN @isOwn := sp.`name` LIKE 'TST %'
        THEN 'O' ELSE 'S'
    END                                     AS 'Group',
    st.`name`                               AS 'Type',
    COUNT(st.`name`)                        AS 'Count',
    DATE(MIN(os.endTime))   AS 'From',
    CASE
        WHEN @isOwn
        THEN TO_DAYS(CURRENT_DATE) - TO_DAYS(MIN(os.endTime)) - 3 + 1
        ELSE TO_DAYS(CURRENT_DATE) - TO_DAYS(MIN(os.endTime)) - 28 + 1
    END                                     AS 'DO'
FROM tdb.orders                             AS os
LEFT OUTER JOIN tdb.shipment_type   AS st
    ON (st.ID = os.shipmentType_ID)
LEFT OUTER JOIN tdb.suppliers       AS sp
    ON (sp.ID = os.supplier_ID)
WHERE
    os.proof IS NULL
    AND os.endTime IS NOT NULL
    AND ((
        sp.`name` NOT LIKE 'TST %' AND 
        (TO_DAYS(CURRENT_DATE) - TO_DAYS(os.endTime)) >= 3
    ) OR (
        sp.`name` NOT LIKE 'TST %' AND
        (TO_DAYS(CURRENT_DATE) - TO_DAYS(os.endTime)) >= 28
    ))
    AND YEAR(os.endTime) = YEAR(CURRENT_DATE)
 GROUP BY
    CASE
        WHEN sp.`name` LIKE 'TST %'
        THEN 'O' ELSE 'S'
    END,
    st.`name`

Answer

Use SET keyword instead of DECLARE. The latter is only needed in stored procedures and functions, to define local variables.

You need an user variable, which is different, for example it is not required to define it and it has no strict type. User variables have @ prefix, local variables don’t. But you can still access it, even from stored procedures/functions.

Also, as commented, you need to separate the two statements (SET and SELECT) with a delimiter ; (or perhaps call them as two statements on the same connection).

MySQL: Is it possible to set a variable outside of SELECT with SELECT as the only permission?

Advertisement

Answer