Author:Mahonri Martinez Date: 09 December 2021

Introduction

Welcome to this blog in which we are going to learn how to create stored procedures. In the word of databases we have several tools for stored data. With a DBMS we can create a lot of things such as databases,tables, functions,stored procedures,etc.

What is a stored procedure?

It is a prepared SQL code that we can use for read data, insert data,delete data and update data. If you use a lot of DML commands, you should use stored procedures.In some cases is better to use stored procedures than the DML command. Sometimes we can implement stored procedures when we are doing a CRUD application.

How to make a stored procedure?

We can make stored procedure in several ways in this blog we are going to show how to make stored procedures in SQL Server and MySQL. This two DBMS are pretty similar there are a little different in the part of stored procedures. SQL Server use the Transct-SQL and MySQL use SQL.SQL Server is a product of Microsoft while MySQL is from Oracle.

In SQL Server we can create a stored procedure following the next scructure

• In this procedure we start with the word create and then procedure or proc are the same then we have to put a name to our procedure. If our procedure use a parameter we can declare as many as we need with @name type separated by a , each parameter. The third part is use the words as and begin. Finally we can use the DML command in this case insert and the word end
• Create procedure nameofprocedure
• @parmaters type
• as begin
• Insert into tablename values(@parmaters)
• END

Example of a stored procedure made in SQL Server

Create a stored procedure in MySQL

In MySQL It is different to create a procedure for insert data we have to use the next word before Create DELIMITER // Then we use the word create procedure and the name of the procedure.Then we have to open (inside here we have to define if the parameter is IN/OUT/INOUT name type ) begin DML command; End //

Example of procedure in MySQL