2
The context in which the problem arose is the following:
I have a stored procedure that performs the registration of certain values in a table of my database.
In this procedure I set all variables as input, so my user must enter some type of value, but not all fields must be filled, only the field that fills my column that is a foreign key.
The fields that do not have to be filled I have the possibility to guide the user to enter "zeros" or automate my application and insert a null value if my user type zero or leave the field blank, I chose the second option, since the storage of "zeros" uses space on my server.
When applying the validation required for the second option, my procedure bumps into errors the user by typing zero or leaving the field blank.
When user inserts "zero" error message 1054 appears.
When you leave the field blank the 1064 error message appears.
The validation I’m using is as follows:
p_n_cliente is an input variable.
if (p_n_cliente = none or '0') then do p_n_cliente = null; end if;
It wouldn’t happen to be:
if (p_n_cliente = none or p_n_cliente = '0') then do p_n_cliente = null; end if;? (supposing thatnoneis a previously defined variable)– anonimo
I tried that way too, but I didn’t get the expected result. When the user typed "zero" he registered "zero" and not "null", and when leaving the field blank he gave error.
– Felipe Vieira
And what type of variable data
p_n_cliente? How is the typed value read? Ever thought about using the functionNULLIF?– anonimo
Well, the data that goes into this variable is like
INT, in this registration form I have other types of variables,FLOATandDATE. About reading the entered value I don’t know how to explain it, I’m using Mysql itself to validate my procedures, so I run the procedure and open the window to enter the values. I don’t know that function, I’ll try to use it.– Felipe Vieira
Well, I made a change in my validation and it was as follows:
if (p_n_cliente = '' or p_n_cliente = '0') 
then set p_n_cliente = null; end if;. Thus the validation is registeringNULLwhen it is inserted0, but still error when the field is left blank.– Felipe Vieira