Check stored procedure in MySql Workbench

Question

I have a stored procedure Insert, which I insert into 2 tables. The second table uses the Last_Insert_ID of the first table. Here is my sproc:

DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `new_user_create`( IN oFarmName varchar(45), IN oFirstName varchar(45), IN oAddress1 varchar(45), IN oCity varchar(45), IN oState varchar(45), IN oZip varchar(45), IN oCountry varchar(45) ) BEGIN insert into intelliair.individual ( FarmName, FirstName) values ( oFarmName, oFirstName); insert into intelliair.address (IndividualID, Address1, City, State, Zip, Country) Values (Last_Insert_ID(), oAddress1, oCity, oState, oZip, oCountry); END 

This is how I test the query in Workbench MySql:

 call new_user_create(@myFarm, @MyName, @MyAddress, @MyCity, @MyState, @MyZip, @MyCountry) 

There appears an error: "Column1 address cannot be null"

Where am I going? Is it in sproc? Or what do I call him?

Answer 1

"Column1 address cannot be null" indicates that the intelliair.address.Address1 field should be specified not null .

And, I don’t think you have predefined the value for @MyAddress before passing it to the stored procedure.
If not defined, it is treated as NULL , and therefore an error occurs.

To cross-check values ​​before calling a stored procedure, for example:

 select @MyAddress; -- ,@myFarm, @MyName, @MyCity, @MyState, @MyZip, @MyCountry; 

Update 1 :

You can call the stored procedure by directly entering values ​​for each of the parameters.
An example :

 call new_user_create( 'my Farm value', -- @myFarm 'E B', -- @MyName 'My Address is SO', -- @MyAddress1 'My City is Coders', -- @MyCity 'CA', -- @MyState '12345', -- @MyZip 'US' -- @MyCountry ); 

Answer 2

An exception is thrown by the INSERT (or UPDATE) statement, which assigns a NULL value to a column named Address1 declared as NOT NULL .

The most likely explanation from what you are showing is that the value passed as parameter oAddress1 is NULL, and an exception is thrown by the second INSERT statement.

Thus, the most likely explanation is that the user variable @MyAddress does not receive a value when the procedure is called.

(You can check if the Address1 column Address1 in the intelliair.individual table, or if it is, it is not defined as NOT NULL .)

(There is also the possibility that this is not one of your statements that throw an exception, but rather a recursive SQL statement, for example, an INSERT statement in a trigger BEFORE INSERTING FOR EACH ROW.)

Answer 3

 USE lportal; DELIMITER $$ CREATE PROCEDURE `iemp`(IN eid INT(11),IN ename varchar(15),IN dname varchar(15),IN doj DATE) BEGIN INSERT INTO e_emp (eid,ename,dname,doj) VALUES (eid,ename,dname,doj); END