Building a Stored Procedure

Building a Stored Procedure with WPDB Power Tool

Assumptions:

  1. You have the WPDB Power Tool installed on your WordPress site
  2. You may or may not know anything about database stored procedures.

Objective:

  1. Lean basic use of WPDB Power Tool Stored Procedure Tool and Building a Stored Procedure with WPDB Power Tool
  2. Learn basics parts of Stored Pocedure
  1. Click on Blank SELECT to populate the Stored Procedure Editor
  2. Delete ## DELETE COMMENTS AT TOP AND BOTTOM AND MODIFY PROCEDURE TO YOUR NEEDS ### at the top
  3. Delete ## DELETE COMMENTS AT TOP AND BOTTOM AND MODIFY PROCEDURE TO YOUR NEEDS ### at the bottom
  4. Change the name “sp_BlankSelect” to “sp_posts_SelectByStatus”
  5. Change “exampleString” to “pageStatus” and “VARCHAR(1000)” to “VARCHAR(20)” this is the same as the field “post_status” in the WordPress posts table. Go ahead and press CTRL+Enter now and you should see your new stored procedure show up in the Stored Procedures list. Hide Built in Stored Procedures to make your new procedure prominent if you like.
  6. Delete the example field with the leading comma ” , IN exampleInt INT(10) ” from the procedure definition (make sure to delete the comma after VARCHAR (20).
  7. Change the line (the wp_ needs to be replaced with whatever prefix your WordPress database uses):
SELECT * FROM table_name 
WHERE str_field = exampleStr OR int_field = exampleInt;

To:

SELECT * FROM wp_posts WHERE post_status = pageStatus;
  1. Now press the Control (CTRL) key and the Enter key at the same time and you should see the message: Procedure sp_posts_SelectByStatus has been updated and logged 
  2. You will also see a DROP appear by it, should you decide to drop it. Now since we can, let’s DROP the new Stored Procedure (don’t worry we are going to get it back)!
  3. Now it should say “PROCEDURE sp_posts_select_ByStatus HAS BEEN DROPPED AND LOGGED SHOULD YOU NEED TO BUILD IT AGAIN.” at the top of the display.
  4. Click on “View Log” and you should see the stored procedure in the display. Now for demo purposes click on “Blank DELETE” to fill the Stored Procedure with some different text.
  5. Now click on your stored procedure in the log display and it should highlight.
  6. Now click in your Stored Procedure editor and Press Control (CTRL) and the A key to Select All
  7. Now Press Control and the V key to paste.
  8. Now press Control Enter again and your stored procedure is back!

This concludes the Building a Stored Procedure with WPDB Power Tool.