Question 21404 in Mysql Question posted in
The official MySQL documentation can be found here.

mySQL Stored Procedure to Set a Calculate field Value

I have a scenario:

I have like 5 Boolean fields in a table, I need to set an option set value based on the number of true values of those 5 Boolean Fields:

Field1
Field2
Field3
Field4
Field5

ENUMField1 values (A,B,C,D,E)

If I have 5 of those Boolean fields as True, I will set the ENUM field value as A,
4 Trues and I set them as B
and so on

As I may need to change the logic of those values in the future , I need to set/change them on DB side on save ,rather than on controller.

Thanks

As I’m not familiar with stored procedures I couldn’t manage to find a solution.

Tags:

3

Answers


  1. 0

    A stored procedure is just putting your SQL on the server rather than calling it from the client. There’s nothing special about it, and it isn’t a way to write logic to run on the server (there are ways to do this, varies on programming language, but the DB is not recommended as a place to store your application code)

    You create a SP with the following syntax. Use your own SQL in the middle.

    CREATE PROCEDURE sp_GetMovies(IN name varchar(50))
BEGIN
    SELECT title, description FROM movies WHERE title LIKE name;
END

    how you call that varies by your client language, but typically its little different than calling a parameterised SQL statement.

    Login or Signup to reply.
  2. 0

    Assuming your enum runs from A-Z then using ascii conversions where dec 65 is A

    select * ,
        cast(char(65+abs(col1-1)+abs(col2-1)+abs(col3-1)+abs(col4-1)+abs(col5 -1)) as char(1)) enumval
from t;

    eg

    drop table if exists t;

    create table t
(col1 tinyint,col2 tinyint,col3 tinyint,col4 tinyint,col5 tinyint, e enum('a','b','c','d')
);

insert into t (col1,col2,col3,col4,col5) 
values
(1,1,1,1,1),
(1,1,1,1,0);

+------+------+------+------+------+------+---------+
| col1 | col2 | col3 | col4 | col5 | e    | enumval |
+------+------+------+------+------+------+---------+
|    1 |    1 |    1 |    1 |    1 | NULL | A       |
|    1 |    1 |    1 |    1 |    0 | NULL | B       |
+------+------+------+------+------+------+---------+

    If you have more and more columns then you will need dynamic sql.

    Login or Signup to reply.
  3. 0

    Maybe generated column is more suitable? It will provide actual value always, rather than stored procedure which must be executed each time when you need in actual values..

    DEMO

    CREATE TABLE test (
  Field1 BOOLEAN, 
  Field2 BOOLEAN,
  Field3 BOOLEAN,
  Field4 BOOLEAN,
  Field5 BOOLEAN);

INSERT INTO test VALUES
(1,1,1,1,1),
(1,1,0,1,1),
(1,0,1,0,1),
(1,1,0,0,0),
(0,0,0,1,0),
(0,0,0,0,0);

SELECT * FROM test;

    Records: 6  Duplicates: 0  Warnings: 0
    Field1 Field2 Field3 Field4 Field5
    1 1 1 1 1
    1 1 0 1 1
    1 0 1 0 1
    1 1 0 0 0
    0 0 0 1 0
    0 0 0 0 0 
    ALTER TABLE test
ADD COLUMN ENUMField1 ENUM('A','B','C','D','E','F')
    GENERATED ALWAYS AS (6-Field1-Field2-Field3-Field4-Field5);

SELECT * FROM test;

    Records: 0  Duplicates: 0  Warnings: 0
    Field1 Field2 Field3 Field4 Field5 ENUMField1
    1 1 1 1 1 A
    1 1 0 1 1 B
    1 0 1 0 1 C
    1 1 0 0 0 D
    0 0 0 1 0 E
    0 0 0 0 0 F

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search