INSERT Stored Procedure Tutorial:
Assumptions:
- WordPress WPDB Power Tool is installed
Objectives:
- Understand advantages of using Stored Procedures
- Understand how to use the WPDB Power Tool to build to build a basic INSERT stored procedure
Stored Procedure Advantages:
- Test data before it is inserted, updated, deleted or selected.
- Perform all of your database logic prior to delivering data and after retrieving it
- Keep all of your SQL in a localized location for easy updating and management
- Deliver a clean record set without having to filter it with code
- Produce cleaner and more elegant code in the application while making it easier to accommodate data changes on in the Stored Procedure without tampering with the code (could not count the number of times this has applied to the real world for me)
The INSERT Stored Procedure Tutorial
So lets create a simple stored procedure that will ensure our NOT NULL rules are followed the way they are intended by creating a Stored Procedure called sp_clients_Insert. The prefix sp_ simply indicates this is a stored procedure. For functions a common convention is fn_ for the prefix and triggers can be tr_ tg_ trig_ whatever you like but make it consistent for the sake of your team and your sanity.
- First lets open up the Stored Procedures tab
- Now lets click on Blank INSERT at the top of the WPDB Power Tool Stored Procedures
- Delete the comment lines at the top and bottom.
- Change sp_BlankInsert to sp_clients_Insert
- Add change exampleStr to firstName and make it the same data type as the one in the ‘my_clients’ by changing VARCHAR(10000) to VARCHAR(64)
- Now lets change exampleInt to clientEmail and make the data type VARCHAR(255) exactly as it is in the table by changing INT(10) to VARCHAR(255)
- Now we need to add one last parameter (we will take care of the REQUIRED DATETIME fields in the Stored Procedure). Lets call it clientWebsite and make it VARCHAR(255). Each parameter must be separated by a comma.
- In good form you can add something meaningful to you Description in the comment section maybe something like “INSERT new Client into my_clients testing for first_name, email and main_website” For Application and Functions leave at none. Now go ahead and press CTRL+ENTER and you should see your stored procedure saved under “Click to View or Edit” in the Stored Procedure list. Feel free to press CTRL+ENTER after making a few entries as much as you like. Make sure to do this before going to another tab to ensure you do not lose your work.
- We are going to do a couple advanced things in this stored procedure to give you an idea of how useful they are and how they can save you a lot of programming headaches. Lets do a data check to make sure we do not already have this first_name, email and main_website. But make sure this is what we have so far:
CREATE PROCEDURE sp_clients_Insert(IN firstName VARCHAR(64), IN clientEmail VARCHAR(255), IN clientWebsite VARCHAR(255))
BEGIN
# Description:
# INSERT new Client into my_clients testing for first_name and email
# Application:
# None - yet
# Function(s):
# None - yet
SELECT * FROM table_name WHERE str_field = exampleStr OR int_field = exampleInt;
END- We need to create a variable store count for us as well as a variable to store the DATETIME in. We do this by adding a line DECLARE theCount INT DEFAULT 0; right after our comments the for our DATETIME on the following line we add DECLARE thisMoment DATETIME DEFAULT NOW(); using the builtin MySQL function NOW() to set the DATETIME. Now lets build the SQL to test for a first_name and email in the table and put our theCount variable to use with: SELECT COUNT(*) INTO theCount FROM my_clients WHERE first_name = firstName AND email = clientEmail; In addition to this we need to verify we have something for email and main_website to ensure we have a clean record to insert. To accomplish this we can use the MySQL functions LENGTH with TRIM (to remove leading and trailing spaces). In addition to this we can do addition SQL verification to ensure we have an @ and a . in the email as a basic test for a valid email.
CREATE PROCEDURE sp_clients_Insert(IN firstName VARCHAR(64), IN clientEmail VARCHAR(255), IN clientWebsite VARCHAR(255))
BEGIN
# Description:
# INSERT new Client into my_clients testing for first_name, email and main_website
# Application:
# None
# Function(s):
# None
DECLARE rowCount INT DEFAULT 0; # For testing rows with this user.
DECLARE thisMoment DATETIME DEFAULT NOW(); # For setting created_date and updated_date
DECLARE firstNameLength INT DEFAULT 0; # For testing for a firstName value
DECLARE emailLength INT DEFAULT 0; # For test for a clientEmail value
DECLARE websiteLength INT DEFAULT 0; # For testing for a clientWebsite value
DECLARE hasAtSign INT DEFAULT 0; # For testing for an @ sign
DECLARE hasPeriod INT DEFAULT 0; # For testing for a .
DECLARE output TEXT DEFAULT ''; # For results output
SELECT LENGTH(TRIM(firstName)) INTO firstNameLength;
IF firstNameLength = 0 THEN
SET output = 'You must enter a first name.<br />';
END IF;
SELECT LENGTH(TRIM(clientEmail)) INTO emailLength;
IF emailLength = 0 THEN
SET output = CONCAT(output, 'You must enter a valid Email.<br />');
ELSE
SET hasAtSign = INSTR(clientEmail, '@');
SET hasPeriod = INSTR(clientEmail, '.');
IF hasAtSign = 0 OR hasPeriod = 0 THEN
SET output = CONCAT(output, 'You must enter a valid Email.<br />');
END IF;
END IF;
SELECT LENGTH(TRIM(clientWebsite)) INTO websiteLength;
IF websiteLength = 0 THEN
SET output = CONCAT(output, 'You must enter a valid website.<br />');
ELSE
SET hasPeriod = INSTR(clientWebsite, '.');
IF hasPeriod = 0 THEN
SET output = CONCAT(output, 'You must enter a valid website<br />');
END IF;
END IF;
# SQL needed to test for email in table here.
# Logic to present whether user was added or already exists Next. Main key
# for any record is email and must be a UNIQUE ID meaning there cannot be two emails
# exactly the same. So they also need to be converted to LOWERCASE when tested in table
END11. With SELECT COUNT(*) we get the number of rows and assign that value to rowCount. At the top where we DECLARE thisMoment we assign the DATETIME value of the moment the stored procedure was called for latter insert. With the SELECT statements we are sending the output of what logic occurred to to whatever application is using the stored procedure. Now we need to setup our logic to let us know the client is or is not in the table but only if the LENGTH(output) = 0. Feel free to press CTRL+ENTER to save your stored procedure.
Now to do something with our rowCount – if there is a row they exist if there is not they don’t. So lets use an if statement. Oh almost forgot we need another variable to store a message to let us know the results of our Stored Procedure. Lets call it output and declare it up at the top with our other declarations making it a TEXT data type (oh its already there). Click on Full Stored Procedure after you have set it up yourself and did a CTRL+Enter to see if your Stored Procedure will save (don’t worry you won’t break anything!).
Now for the logic to test for the email being already present in the table. At this point you will get one hint to work with. You will need to use a IF ELSE within a IF to determine whether you have anything in the output variable up to this point. If the output has a length greater than zero there is a problem with the data being passed to the stored procedure. If not the record might be worthy to be added. See if you can complete the logic in the IF and the nested IF ELSE where the email was found and no more needs to be done. Else will proceed to add the first_name, email and main_website to the clients table and provide an appropriate output message.
At this point we are about finished. All information has been provided to complete this INSERT Stored Procedure Tutorial. However there is much to be done to prove to yourself you understand all that has been provided here. For example you can add Phone number and Last Name to the INSERT process. You may also provide a note or allow the user to provide a note. The notes field is a TEXT data type which allows for 65,535 characters. Or about 22 pages worth of text. So feel free to experiment with this and add as many rows as you like. Because of email and main_website being UNIQUE field types they actually will throw an error preventing the INSERT (or an UPDATE) should you attempt to put a duplicate entry into these fields.
This concludes our INSERT Stored Procedure Tutorial. For additional fun and learning there is a error in the completed Stored Procedure for you to fix if you decided to copy and paste it into the Stored Procedure Editor. Next recommended tutorial is the Testing Stored Procedure Tutorial.