SQL Case Statement in a Function

Question

I am trying to write a function that takes two parameters and returns a calculated result based on a case statement (please see below). I keep getting a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE when (medToConvert) = "Codeine" then MME = doseToConver' at line 13

This is what I've tried so far:

    /* Function that takes two parameters as input:
   Dosage of an opioid
   Name of the opioid

   Returns the morphine equivalent dosage */

    CREATE FUNCTION convertToMorphineEquiv (doseToConvert INT, medToConvert VARCHAR(20))
    RETURNS INT

    BEGIN
        DECLARE MME INT

        CASE       
                 when (medToConvert) = "Codeine" then MME = doseToConvert * 0.15

                 -- Fentanyl Transdermal (in mcg/hr)
                 when (medToConvert) = "Fentanyl" then MME = doseToConvert * 2.4

                 when (medToConvert) = "Hydrocodone" then MME = doseToConvert * 1
                 when (medToConvert) = "Hydromorphone" then MME = doseToConvert * 4
                 when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 1 AND 20 then MME = doseToConvert * 4
                 when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 21 AND 40 then MME = doseToConvert * 8
                 when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 41 AND 60 then MME = doseToConvert * 10
                 when (medToConvert) = "Methadone" AND doseToConvert >=60 then MME = doseToConvert * 12
                 when (medToConvert) = "Morphine" then MME = doseToConvert * 1
                 when (medToConvert) = "Oxycodone" then MME = doseToConvert * 1.5
                 when (medToConvert) = "Oxymorphone" then MME = doseToConvert * 3
                 when (medToConvert) = "Tapentadol" then MME = doseToConvert * 0.4

                 else "Conversion for this opioid is not available"
        END

         RETURN MME
    END

Please edit your question and add the exact error message — a_horse_with_no_name
You mix Valued and Searched Case, you must do Searched only, i.e. CASE when (medToConvert) = "Codeine" then ... when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 1 AND 20 then.. — dnoeth
Have you considered putting that data in a table instead of hard-coding it in a function? It would make it a LOT easier to add new conversions when new medicines are released. — D Stanley

John Zabroski John Zabroski · Accepted Answer · 2016-10-19T21:01:43

Create a table instead, and join to it. You will get much faster performance using a CROSS APPLY operation, as scalar-valued user-defined functions suffer from RBAR (Row By Agonizing Row) performance penalties.

CREATE TABLE dbo.MedicineDoseConversion (
  medicine_name varchar(20) not null,
  dose_to_convert_min_units int null,
  dose_to_convert_max_units int null,
  dosage_multiplier decimal(18,10) not null
)
GO

INSERT dbo.MedicineDoseConversion (medicine_name, dose_to_convert_min_units, 
dose_to_convert_max_units, dosage_multiplier)
SELECT 'Codeine', null, null, 0.15 UNION ALL
SELECT 'Fentanyl', null, null, 2.4 UNION ALL
SELECT 'Hydrocodone', null, null, 1 UNION ALL
SELECT 'Hydromorphone', null, null, 4 UNION ALL
SELECT 'Methadone', 1, 20, 4 UNION ALL
SELECT 'Methadone', 21, 40, 8 UNION ALL
SELECT 'Methadone', 41, 60, 10 UNION ALL
SELECT 'Methadone', 60, null, 12 UNION ALL
SELECT 'Morphine', null, null, 1 UNION ALL
SELECT 'Oxycodone', null, null, 1.5 UNION ALL
SELECT 'Oxymorphone', null, null, 3
;
GO

SQL Case Statement in a Function

1 Answers