test a stored procedure in MySql Workbench

Question

I have an Insert stored procedure where I am inserting into 2 tables. The second table using 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

Here is how I am testing the query in MySql workbench:

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

There error I get is: "Column Address1 cannot be null"

Where am I going wronng? Is it in the sproc? Or the way I am calling it?

Solution

"Column Address1 cannot be null" indicates that the intelliair.address.Address1 field must be defined not null.

And, I don't think that you pre defined value for @MyAddress before passing it to the stored procedure.
Unless defined it is treated as NULL and hence is the error thrown.

To cross check values before calling the stored procedure like :

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

Update 1:

You can call stored procedure by directly inputting values for each of the parameters.
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
);

Comments

[EB.]

thank you. this is where i am confused. I am not exactly sure hot to test (call) the sproc. The values I want inserted into the table is : MyAdress. But I thought the syntax was : @Myaddress. How would I properly call this sproc using actual values?

[EB.]

Also, when i remove the @ symbol and just use MyName as the actual value. It tells me that Myname column doesn't exist.

[Ravinder Reddy]

You can just input the actual values while calling, unless interested in variables. Like call my_sp( 'value1', 'value2', .. ).