Johno Johnson Johno Johnson - 1 year ago 74
MySQL Question

Inserting a row in SQL and setting up variables in a stored procedure?

I was wondering if anyone knew or if it is possible to set (id, FirstName, LastName) inside the procedure below so I could insert new customers by typing...

CALL ('1','John','Smith');
CALL ('2', 'Jane','Smith'); and so on.

At the moment my code looks like this. I am using phpMyAdmin

CREATE PROCEDURE spUpdateCustomers(id INT(10),
FirstName VARCHAR(30),
LastName VARCHAR(30))
INSERT INTO customers (CustomerID, CustomerFirstName, CustomerLastName) VALUES (id , FirstName, LastName);

Then I call it using.

SET @id='1';
SET @FirstName='John';
SET @LastName='Smith';
CALL spUpdateCustomer(@id, @firstName, @lastName);

I am aiming to avoid setting each variable before every call. Any help would be appreciated!

Answer Source

You do not need to define variables for parameter values:

CALL spUpdateCustomer(1, 'John', 'Smith');

Note: The first parameter of your procedure is defined as an integer. MySQL implicitly casts '1' to 1 (string to integer). Nevertheless, if you are executing the procedure on the database server, it would be a good practice to use strictly integer values for integer parameters.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download