Overview Of SQL Stored Procedures
Tags: mysql, database
Published: 2022-02-14 14:41:55

Hello, This Blog is about __MYSQL *Stored Procedure*__. Think of it as a beginner guide or intro towards Stored Procedures.To follow along, you must at least know the basics understanding of MYSQL operations like creating a database/ table and simple _DML_ (*Data Manipulation Language*) queries __(INSERT, UPDATE AND DELETE)__ and  _DQL_ (*Data Query Language*) queries __(SELECT)__.

> Feel Free to comment down below you find any mistake or suggestion to improve 😊

## *Let's start*

---

### What ? is Stored Procedure?

> In in a simple term, it’s just the bunch of SQL instructions (queries) run together, just like a function in other programming languages.


> A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed. – W3School

### When ? to use it?
Let say, When you have a set of instructions that need to run one after another sequence and you miss something it’s might be troubling. Run a single query would be tedious. But creating a stored procedure for the mundane task, it would be easy to run in a single stored procedure call.

### Where ? to use it?
Periodic processing of data is necessary.

### Why ? to use it?

* They allow modular programming.
* They allow faster execution.
* They can reduce network traffic.
* They can be used as a security mechanism
* If you invoke the same stored procedure in the same session again, MySQL just executes the stored procedure from the cache without having to recompile it.

> *Below query will run on __MariaDB__ MYSQL but for other SQL, some commands may differ.*

---

### Let’s get querying !

For below example, we consider the below Product table


| id            | name	        | sku            | qty           | status        |
| ------------- |:-------------:| :-------------:|:-------------:|:-------------:|
| 1             | Apple         |  10000034      | 50            | 1          | 
| 2             | Pan           |  10000032      | 10            | 0          |
| 3             | Desktop       |  10000031      | 1             | 1          | 
| 4             | mouse         |  10000030      | 23            | 1          | 
| 5             | graphic card  |  10000021      | 56            | !          | 



### What is DeLimiter? How to change it?

> A _delimiter _is a sequence of one or more characters for specifying the boundary between separate, independent regions in plain text or other data streams – [wikipedia.org](https://en.wikipedia.org/wiki/Delimiter#:~:text=A%20delimiter%20is%20a%20sequence,sequence%20of%20comma%2Dseparated%20values.)

When you write SQL query, you use the semicolon (;) to separate two statements like the following example:

```sql
SELECT name FROM Product;
SELECT sku FROM Product;
```

In case of SQL Default, Delimiter/End of SQL query is a semicolon (;) But to change the default delimiter to the dollar ($) like the following exampl:


```
DELIMITER $$

```
Delimiter Character may consist of single or double characters like **//** or **$$** however you should avoid using backslash since it _escapes sequence_.

When creating a stored procedure from the command-line, you will likely need to differentiate between the regular delimiter and a delimiter inside a BEGIN END block. To understand better, consider the following example:

```mysql
CREATE PROCEDURE  PROCEDURE_NAME() 
 BEGIN
 SELECT name FROM customer;
END;
```

If you enter above code line by line in command line then, MYSQL client will interpret the _semicolon (;)_ as the end of the statement and will throw an exception. So, we temporarily change the delimiter other than the default

## 🎉How Write your own the Stored Procedure?🎉

We start with by changing the default Delimiter. All the SQL statements will be wrapped around BEGIN and END keyword as below

```mysql
DELIMITER $$
  CREATE PROCEDURE PROCEDURE_NAME() 
   BEGIN
    /* Procedure code  */ 
    END $$
DELIMITER ;
```

If a _Stored Procedure_ with the same name already exists, it will throw an exception 

> _procedure with the same name already exists_

To Run Stored Procedure Run Below SQL Query

```mysql
CALL PROCEDURE_NAME();
```

Drop a Stored Procedure

If a stored procedure is not present, it will throw an exception. To prevent it,we can add a Conditional check `[IF EXISTS ]` clause

```mysql
DROP PROCEDURE PROCEDURE_NAME;
    /* OR */
DROP PROCEDURE IF EXISTS PROCEDURE_NAME; 
```

Get the list of stored procedures : 

```mysql
SHOW PROCEDURE STATUS;
```

```mysql
Output : –

                  Db: testSP
                Name: udpateStatus
                Type: PROCEDURE
             Definer: root@%
            Modified: 2020-02-24 13:17:58
             Created: 2020-02-24 13:17:58
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

```

_To Filter the result, you just need to add WHERE Clause as below._

```mysql
SHOW PROCEDURE STATUS WHERE Db= DATABASE_NAME;
```
Conclusion

In my next post, I will explain more about store procedures like variables, conditions, loop, and cursor.