As a C# developer who did database work more than 7 years ago (with MSSQL) I am now developing a MySQL Database solution where Inline SQL is prohibited in code and the developers must go through Stored Procedures (Routines).
The core challenge is defining CONST/STATIC values on the database side in one place that all of my stored procedures can access. For example:
# == RECORD STATE SET GLOBAL RECORD_STATE_NORMAL = 0; SET GLOBAL RECORD_STATE_ARCHIVED = 1; SET GLOBAL RECORD_STATE_DELETED = 2;
These values will never change and the database stored procedures need to use them when writing certain record details. I also need to share these values with the developers as they may need to pass core static defined values to some of their stored procedure calls.
The core problem is MySQL apparently refuses to let me create and set my own global variables outside of the configuration file (which I don't want to do since we're deployed on AWS RDS). Any time I try to set a global variable I get an error 1193 "unknown system variable".
I need a way to define a long list of Global static values on the database side (simple numbers) that all of my stored procedures can reference.
How can I do this?