A stored procedure is called recursive if it calls itself. Basically, this concept is called recursion. MySQL limits the recursion so the errors will be less rigorous. We can check this limit with the help of the following query −mysql> Show variables LIKE '%recur%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | max_sp_recursion_depth |   0   | +------------------------+-------+ 1 row in set (0.01 sec)We can change this value up to 255 with the help of the following query −mysql> SET @@GLOBAL.max_sp_recursion_depth = 255// Query OK, 0 rows affected (0.00 sec) mysql> Show variables LIKE ... Read More

Following is a stored procedure which fetches the records from name column of table ‘student_info’ having the following data −mysql> Select * from Student_info; +-----+---------+------------+------------+ | id  | Name    | Address    | Subject    | +-----+---------+------------+------------+ | 101 | YashPal | Amritsar   | History    | | 105 | Gaurav  | Chandigarh | Literature | | 125 | Raman   | Shimla     | Computers  | | 127 | Ram     | Jhansi     | Computers  | +-----+---------+------------+------------+ 4 rows in set (0.00 sec) mysql> Delimiter // mysql> CREATE PROCEDURE cursor_defined(OUT val ... Read More

As we know that CONCAT() function returns NULL if any of the arguments is NULL but CONCAT_WS() function returns NULL only if the first argument i.e. the separator is NULL and it ignores any other NULL. We can say this is the advantage of CONCAT_WS() function over CONCAT() function when we want to concatenate the values from the column and any of the columns have NULL as its value. To understand it, we consider the example from the table ‘Student_name; which have the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName   | Mname | Lname   | ... Read More

MySQL FORMAT() function, converts a number to a format like #, ###, ###.### which is rounded up to the number of decimal places specified and returns the result as a string, can be used to retrieve the output having decimal values of a column in a specified format. To understand it, we are taking an example of table ‘estimated_cost’ which have the following data −mysql> Select * from estimated_cost; +----+-----------------+-----------+---------------+ | Id | Name_Company    | Tender_id | Tender_value  | +----+-----------------+-----------+---------------+ | 1  | ABC Ltd.        | 110       | 256.3256879   | | 2 ... Read More

We must have to declare NOT FOUND handler while working with MySQL cursor because it handles the situation when cursor could not find any row. It also handles the situation when the cursor reaches the end of the row because every time we call FETCH statement the cursor finds to attempt the next row in the result set. Following is the syntax to declare NOT FOUND handler −DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_name = value;Here var_name is the name of any variable and value would be the value of that variable. For example, we can declare it as ... Read More

The above-said property is not useful especially in the case when we want to concatenate the values from the column and any of the columns have NULL as its value. To overcome this, we can use IFNULL() function along with CONCAT() function. To understand it, we consider the example from the table ‘Student_name; which have the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName   | Mname | Lname   | +---------+-------+---------+ | Rahul   | NULL  | Singh   | | Gaurav  | Kumar | NULL    | | Harshit | NULL  | Khurana | | Yash ... Read More

Actually, CONCAT_WS() function returns NULL if and only if the first argument of it i.e. the separator is NULL. An example is as below −mysql> Select CONCAT_ws(NULL, 'Tutorial', 'Point', '.com'); +-------------------------------------------+ | CONCAT_ws(NULL, 'Tutorial', 'Point', '.com') | +-------------------------------------------+ | NULL                                      | +-------------------------------------------+ 1 row in set (0.00 sec)Otherwise, MySQL CONCAT_WS() function ignores NULL if we place NULL at any other position in CONCAT_WS() function while linking the strings. Following examples will exhibit it −mysql> Select CONCAT_ws('s', 'Tutorial', 'Point', '.com', NULL); +-----------------------------------------------+ | ... Read More

String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. Actually, max_allowed_packet is a dynamic global variable which can accept the integer type values. These values can be set for a session only. It can accept 1024 as the minimum value and 1073741824 as the maximum value. The by the default value of this system variable is 1048576.

MySQL CONCAT() function will return a NULL if you will add a NULL value while linking two strings. Following example will demonstrate it −Examplemysql> Select CONCAT('Tutorials',NULL,'Point'); +----------------------------------+ | CONCAT('Tutorials',NULL,'Point') | +----------------------------------+ | NULL                             | +----------------------------------+ 1 row in set (0.06 sec) mysql> Select CONCAT('TutorialsPoint','.com',NULL); +--------------------------------------+ | CONCAT('TutorialsPoint','.com',NULL) | +--------------------------------------+ | NULL                                 | +--------------------------------------+ 1 row in set (0.00 sec)

As we know that MySQL NULLIF() control flow function will return NULL if both the arguments are the same, otherwise it returns the first argument. Hence it is similar to the following CASE statement −CASE WHEN expression1=expression2 THEN NULL ELSE Expression2 END.