Stored Procedures in WordPress

Stored Procedures in WordPress is not common. In fact WordPress does not at present use stored procedures for the SQL management and relies heavily on code objects. Using stored procedures to create Themes, Plugins and Templates is a more elegant solution with much less code maintenance. The reason for this elegance is you separate the database objects completely from the code. Allowing for longer life cycle as well as easier upgrades to new MariaDB features. In addition to this it is far more difficult to perform SQL injections (hacking the data) on a stored procedure. Within this plugin are many examples of Stored Procedures with access to the code with the Plugin Editor.

When building a stored procedure you can use parameters however you can also scrub the input data prior to displaying a recordset, updating a record, deleting or inserting. This tool also provides numerous examples of this process as well. The WPDB Power Tool uses the WordPress $wpdb object exclusively to call all of the stored procedures built into this plugin. On the $wpdb Object usage Examples page you will see all of the $wpdb methods used by this plugin as a quick reference.

Along with the examples of the plugins stored procedures used for WordPress it has a Blank INSERT, Blank SELECT, blank UPDATE and blank DELETE to get you started on any stored procedure for your convenience. All of the stored procedures used in this plugin identify where they are used and the PHP function they are used in. This plugin uses AJAX extensively for database calls. Here is an example of the documentation in one of the stored procedures:

#### THIS IS FOR VIEWING ONLY ####
CREATE PROCEDURE sp_GetProcedureDefinition(IN routine VARCHAR(64), IN procSchema VARCHAR(64))
BEGIN
	# Description:
	#	Procedure used to build stored procedure for display in editor.
	#	works in tandem with sp_GetProcedureParameters
	# Application:
	#	admin/stored_procedures_ajax.php
	# Function:
	#	function GetDefinition

	SELECT ROUTINE_DEFINITION AS output FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE SPECIFIC_NAME = routine AND ROUTINE_SCHEMA = procSchema;
END
###### THIS WILL NOT MODIFY ######

In the Plugin Editor you would browser to the file stored_procedures_ajax.php in the admin directory and search for the function GetDefinition for a full code example of how the procedure is called and used in the plugin.