How to assign a null value to an input variable if the user leaves the field blank or type zero?

Asked

Viewed 126 times

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;

mysql database validation stored-procedures

  • 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 that none is a previously defined variable)

  • 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.

  • And what type of variable data p_n_cliente? How is the typed value read? Ever thought about using the function NULLIF?

  • Well, the data that goes into this variable is like INT, in this registration form I have other types of variables, FLOAT and DATE. 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.

  • 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 registering NULL when it is inserted 0 , but still error when the field is left blank.

No answers

Browser other questions tagged mysql database validation stored-procedures

You are not signed in. Login or sign up in order to post.