SELECT Stored Procedure Tutorial – WPDB Power Tool WordPress Table Utility

SELECT Stored Procedure Tutorial – The fifth tutorial in the Query Tool and Stored Procedure Tutorial.

The SELECT Stored Procedure Tutorial

Assumptions:

First you have built the table clients from the SQL Basics Tutorial TABLES.
Second you have built the stored procedure sp_clients_ Insert from the INSERT Stored Procedure Tutorial and have some data to work with.
You understand the basics of the SELECT statement covered in the SQL Basics Tutorial SELECT tab.

Objectives:

Understand how to build a simple SELECT stored procedure with the WPDB Power Tool.
Understand how to pass parameters to be used for switches determining what output results will be.

Consequently, without the above being completed, at minimum, this Tutorial will be more challenging. That being made clear let’s get started.

Since we now have some clients, we may need to see them for various purposes. Perhaps to send informational email about your service. As well as updating them on business changes. Accordingly you may want to get their phone number or email to contact them? Therefore, let’s build a stored procedure to do three things:

Display all clients ordered by date created ordered descending limiting the rows to 500 (show us the 500 most recent new clients)
Get client’s email, phone and website by first name
Get all client information by email

Let’s call this stored procedure sp_clients_Select with two parameters getBy and thisValue. To start, open up the Stored Procedure tab and click on Blank SELECT at the top of the Stored Procedure tool.

Remove the comment lines at the top and bottom (they tell you where they are)
Rename sp_BlankSelect to sp_clients_Select and since both of these parameters are not going into the table we only need to consider what we are taking out of the table and if that relates to the parameters going in. For getBy ALL we are only determining ASC (ascending) or DESC (descending but also used as DESCRIBE depending on the SQL preceding it). We can use the ASC or DESC in the SQL. For finding clients by first name we need the name parameter to be as big as the first_name field which is?

In that case, let’s build the start of our procedure then press CTRL+Enter to save it. Given these points, you can either look at the stored procedure as it should look (or close to it) up to this point or build it yourself then compare it to what is here.

CREATE PROCEDURE sp_SelectClientsBy (IN getBy VARCHAR(20), IN getValue VARCHAR(64))
BEGIN
# Description:
# Procedure to get All Clients DESC or ASC by date, Client email and phone by first_name or clients with missing data
# Application:
# None
# Function(s):
# None

SELECT * FROM table_name WHERE str_field = exampleStr OR int_field = exampleInt;
END

Now to setup our initial logic. We are going to have three conditions for getting data. All, Name or Empty (this will be useful if you left all of the test entries from the INSERT tutorial). We can do this with a CASE statement or a IF statement. Thus far, we have already used the IF statement, so just for fun let’s use the case statement in this tutorial (don’t worry by the end of these tutorials you will have a number of logic tools at your disposal). However, in the last tutorial we learned it might be a good idea to test the data in the parameters coming in. Thus, we will do that first with sudo code first as follows:
IF getVal equals All OR it Equals Name OR it equals Empty THEN
Find out what it is and display it with
CASE All,
CASE Name or
CASE Empty
END IF;

Now let’s get our IF part done assigning ‘All’, ‘Empty’ and ‘Name’ as the only valid values and start our CASE in our IF as follows:

CREATE PROCEDURE sp_SelectClientsBy(IN getBy VARCHAR(20), IN getValue VARCHAR(64))
BEGIN
    # Description:
    #	Procedure to get All Clients DESC or ASC by date, 
    #   Client email and phone by first_name or clients with missing data
    # Application:
    #	None
    # Function(s):
    #	None
	
    DECLARE runSQL VARCHAR(100);
	
    IF getBy = 'Name' OR getBy = 'All' OR getBy = 'Empty' THEN
        CASE getBy
	    WHEN getBy = 'All' THEN
		SELECT 'All';
	    WHEN getBy = 'Empty' THEN
		SELECT 'Empty';
	    WHEN getBy = 'Name' THEN
		SELECT 'Name';
	END CASE;
    END IF;
END

The SELECT ‘All’, SELECT ‘Empty’ and SELECT ‘Name’ need to be replace with the SQL to get what you need for each of these cases. See if you can complete the SELECT ‘Empty’ and the SELECT ‘Name’ completed without looking at the answer. The SELECT all is a little different and requires that we build the SQL before we run it (which will add another tool to your nifty SQL tool box!).

CREATE PROCEDURE sp_SelectClientsBy(IN getBy VARCHAR(20), IN getValue VARCHAR(64))
BEGIN
    # Description:
    #	Procedure to get All Clients DESC or ASC by date, Client email and phone by first_name or clients with missing data
    # Application:
    #	None
    # Function(s):
    #	None
	
    DECLARE runSQL VARCHAR(100);
	
    IF getBy = 'Name' OR getBy = 'All' OR getBy = 'Empty' THEN
	CASE getBy
    	    WHEN 'All' THEN
		SELECT 'All';
	    WHEN 'Empty' THEN				
		SELECT * FROM my_clients 
                WHERE TRIM(first_name) = '' OR email = '' OR main_website = '' OR
		    created_date = '0000-00-00 00:00:00' OR updated_date = '0000-00-00 00:00:00';
	    WHEN 'Name' THEN				
		SELECT first_name, last_name, email, phone, main_website FROM my_clients 
		WHERE first_name = getValue ORDER BY first_name, last_name;
	END CASE;
    END IF;
END

Finally, the last piece: The All choice. This one requires us to build the SQL inside the stored procedure before running it as you cannot pass a parameter to the ORDER BY clause. In other words, if you try to use the SQL SELECT * FROM my_clients ORDER BY created_date getValue; ,you will get an error. A very unrelated one at that. You might go ahead and add this for your CASE WHEN ‘All’ and see the error. So to get around this we will use CONCAT to build the SQL string then EXECUTE it then empty the variable. In case you were wondering what the DECLARE runSQL VARCHAR(100); was for you are about to find out.

To do this see how the ‘All’ is setup below:

CREATE PROCEDURE sp_SelectClientsBy(IN getBy VARCHAR(20), IN getValue VARCHAR(64))
BEGIN
    # Description:
    #	Procedure to get All Clients DESC or ASC by date, 
    #   Client email and phone by first_name or clients with missing data
    # Application:
    #	None
    # Function(s):
    #	None
	
    DECLARE runSQL VARCHAR(100);
	
    IF getBy = 'Name' OR getBy = 'All' OR getBy = 'Empty' THEN
    	CASE getBy
     	    WHEN 'All' THEN
		IF LENGTH(getValue) > 0 AND (getValue = 'ASC' OR getValue = 'DESC') THEN
	 	    SET @tempSQL = CONCAT('SELECT * FROM my_clients ORDER BY created_date ', getValue);
		    PREPARE runSQL FROM @tempSQL;
		    EXECUTE runSQL;
		    DEALLOCATE PREPARE runSQL;
		ELSE
		    SELECT * FROM my_clients ORDER BY created_date;
		END IF;
	    WHEN 'Empty' THEN				
	        SELECT * FROM my_clients WHERE TRIM(first_name) = '' OR email = '' OR main_website = '' OR
		    created_date = '0000-00-00 00:00:00' OR updated_date = '0000-00-00 00:00:00';
	    WHEN 'Name' THEN				
	        SELECT first_name, last_name, email, phone, main_website FROM my_clients 
	        WHERE first_name = getValue ORDER BY first_name, last_name;
	END CASE;
    END IF;
END

In order to pass such a parameter to a SQL that is part of the SQL you must build the SQL first. This applies to tables (say if you want to dynamically pick a table to select from, I know it sounds weird but it happens in the SQL world) or if you need to dynamically pass a field name to a SQL statement (say you want to specify the fields you want to display) you will need to use this example. SET @tempSQL sets a temporary variable that will be used only for this stored procedure call. PREPARE loads our declared string container runSQL with the SQL we want to run. EXECUTE then runs the SQL and DEALLOCATE PREPARE ensures we leave nothing behind in the runSQL container.

This concludes our SELECT Stored Procedure Tutorial. Now it is off to the DELETE Stored Procedure Tutorial so we can get the data cleaned up for the UPDATE Stored Procedure Tutorial! If you have time that is. Or would like to know how to use the WPDB Power Tool to build an DELETE Stored Procedure. Or you are just bored to tears!