Overview Of SQL Stored Procedures
Tags: mysql, database
Published: 2022-02-16 13:03:39

Hello Everyone, In the last post, I have covered the basics of how to create and delete Store procedures. In this post, I will go through the below topics and how to use in, Store procedures

- Variables
- Conditional statement
- Switch statement
- Loop
- Cursors
- Variable

### Variables
Unlike other programming languages, a Variable is just a placeholder for the actual value. As the meaning of variable is, not consistent or having a fixed pattern; liable to change. Every Variable has 2 things i.e _Name_ and _DataType_. To define/declare a variable. As shown below :

`DECLARE VARIABLE_NAME DATATYPE DEFAULT VALUE`

Example :

```mysql
DECLARE done INT DEFAULT FALSE;
```

### Conditional Statement
To handle the Logical behaviour of the SQL statement, we use the conditional statement. They allow us to handle the condition based upon a particular scenario. As shown below :

`IF CONDITION THEN
    /* Statement */
ENDIF;`

or alternative syntax
`IF(CONDITION,TRUE VALUE, FALSE VALUE)`

If the condition is satisfied, then the code surrounded by the IF block is executed. if the code has else block then the ELSE block is executed. You also have nested block as shown below :

```mysql

    IF CONDITION THEN
        /* Statement */
        IF CONDITION THEN
            /* Statement */
        ELSE 
            /* Statement */
        ENDIF;
    ELSE 
       /* Statement */
    ENDIF;
```

To handle cases where the logical behaviour many outcomes. You can also have if-else-if-else or IF-ELSE-IF ladder. As shown below.

```mysql
    IF CONDITION THEN
        /* Statement */
    ELSE IF CONDITION THEN
       /* Statement */
    ELSE 
       /* Statement */
    ENDIF;
```
Example :

```mysql
    SELECT id,
        IF(`qty` > 0 && `stock_status` = 1,'ENABLE','DISABLE') as product_enable
    FROM products;
```

### Switch Statement

An Alternate to IF-ELSE-IF ladder is SWITCH CASE, were operation is performed based upon the input value. As shown below.

`    WHEN
        CASE CONDITION THEN /* Statement */
        CASE CONDITION THEN  /* Statement */
        ELSE  RESULT 
    END CASE;`
Example :

```mysql
    SELECT id,
        (
        CASE
            WHEN  `qty` > 0 && `stock_status` = 1 THEN 'ENABLE'
            WHEN  `qty` <= 0 && `stock_status` = 0  THEN 'DISABLE'
            END
        ) as product_enable
    FROM products;
```
### Loop Statement
Loop is crucial to any programming language as it allows us to perform the same task iteratively if the condition is true. There are different variant of _LOOPS WHILE_ and just a simple _LOOP_. As shown below :

`WHILE LOOP
    WHILE expression DO
        /* Statement */
    END  WHILE;`

Loop will go-on until the condition is satisfied. when false the execution of block stop

`LOOP
    LOOP_NAME: LOOP
        /* Statement */
    END LOOP;`

You can leave LOOP if a certain case is true. As shown below :

`LEAVE LOOP
    LOOP_NAME: LOOP
        IF CONDITION THEN
            LEAVE LOOP_NAME;
        END IF;
        /* Statement */
    END LOOP;`

### Cursors
What is Cursors ?
A cursor allows you to iterate a set of rows returned by a query and process each row individually.

For any MySQL cursor, there are 5 parts ,

* Declaration Statement
   `DECLARE CURSOR_NAME CURSOR FOR SELECT_SQL_STATEMENT`
* Handler Statement : Handler allows us to handle the scenario when the cursor hits the end of the rows.
   `DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;`
* Open Statement
   `OPEN CURSOR_NAME;`
* Fetch Statement
   `FETCH CURSOR_NAME INTO VAR1`
* Close Statement
   `CLOSE CURSOR_NAME;`

Complete Syntax :

```mysql
    DECLARE VAR1  DATA_TYPE;
    DECLARE CURSOR_NAME CURSOR FOR SELECT FIELD FROM TABLE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN CURSOR_NAME;
        FETCH CURSOR_NAME INTO VAR1;
    CLOSE CURSOR_NAME;
```
Example of Loop & Cursor :

```mysql
DELIMITER $$
    CREATE PROCEDURE updateStatus()
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE id INT;
        DECLARE cursor_products CURSOR FOR SELECT Id FROM products;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN cursor_products;

        read_loop: LOOP
            FETCH cursor_products INTO id;
            IF done THEN
                LEAVE read_loop;
            END IF;
            UPDATE products set stock_status=IF(qty>0,1,0) WHERE Id=id;
        END LOOP;

        CLOSE cursor_products;
    END$$
DELIMITER ;
```
## Conclusion: 
To learn more about store procedures visit the below reference links as a starting point.

All the Best. To embark on the journey towards the stored procedure. 😊 😊

**_Happy Coding!_**
_Reference_
* [SQLSERVERTUTORIAL ](https://www.sqlservertutorial.net/)
* [Learn Material mysqltutorial](https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/)
* [TECHREPUBLIC](https://www.techrepublic.com/article/determine-when-to-use-stored-procedures-vs-sql-in-the-code/)
* [wikipedia](https://en.wikipedia.org/wiki/Delimiter)
* [w3schools](https://www.w3schools.com/sql/sql_stored_procedures.asp)