As well as the functions in programming, procedures are used in the database to store features that may later be used, providing thus reuse of codes in the database. Queries, inserts, updates, and removal of records in tables are done through an editor, whose codes will be lost at the end of the session and, if necessary use them later, they will have to be rewritten.

In the procedures, the commands are stored in the same format as a method. Each procedures is stored in a specific location within the database and can be used as often as needed.

In MYSQL, procedures are created using the following syntax:

CREATE PROCEDURE ‘[name of the procedure]‘ ([Parameters])




You can work with procedures that process parameters, that return consultations, that work with transactions and a series of other situations. All that need to be done in a database is possible with the use of procedures.

To present some example, we will create a simple procedure that will return a query from a code informed by a user:

CREATE PROCEDURE ‘sp_list_product’ (IN _id INT)



FROM products

WHERE id_product = _id


Observe the procedures involving parameters: in such cases, one must indicate the type of variable that will be informed in the procedure call. This, in turn, must be compatible with the type of query or action that will be done. Moreover, it is necessary to indicate the “mode” of the parameter before the name, and they can be:

  • IN: Indicates that the parameter is only for receiving values and cannot be used for feedback;
  • OUT: Indicates an output parameter. In this case, it is not passed any value in the call procedure;
  • INOUT: This type of parameter can be used for both purposes (input and output).

Another important point is regarding the CREATE syntax. After the procedure is created, you must use the ALTER command so that subsequent amendments are applied. If the command is not modified, the database reports an error indicating that there is already a procedure created by that name.

As for naming, using “sp_” before the name of the procedure helps to standardize the database.

Finally, the procedure call in the database’s management screen will be as follows:

call sp_list_product(1)

If the parameter is not informed or the informed parameter is of the wrong type, an error message will appear.


In Scriptcase it is possible to use customized procedures in forms, for example. You just need to create them directly in the database and then display it in the tool.

Check out more content on our blog!
Learn all about Scriptcase.

By ,

May 19, 2015


You might also like…

“We have developed the Connected Citizen Card, which assists public administrators in decision-making and policy creation.”(Assist Soluções em TI)

Check below how Assist Soluções em TI used Scriptcase to modernize and streamline the development...

AI-Driven Software Development: The Role of ChatGPT

In the rapidly evolving world of technology, artificial intelligence (AI) is playing an increasingl...

Low-Code: The Key to Accessible Digital Transformation

Digital transformation is imperative for companies wishing to remain competitive in the current era...

You might also like…

Get new posts, resources, offers and more each week.

We will use the information you provide to update you about our Newsletter and Special Offers. You can unsubscribe any time you want by clinck in a link in the footer of any email you receive from us, or by contacting us at Learn more about our Privacy Police.