- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
In MySQL stored procedures, how to check if a local variable is null?
For this, use COALESCE(). Let us implement a stored procedure to check if the local variable is null −
mysql> DELIMITER // mysql> CREATE PROCEDURE local_VariableDemo() BEGIN DECLARE value1 int; DECLARE value2 int; select value1,value2; select concat('After checking local variable is null the sum is = ',COALESCE(value1,0)+COALESCE(value2,0)); END // Query OK, 0 rows affected (0.19 sec) mysql> DELIMITER ;
Call the stored procedure using CALL command −
mysql> call local_VariableDemo();
This will produce the following output −
+--------+--------+ | value1 | value2 | +--------+--------+ | NULL | NULL | +--------+--------+ 1 row in set (0.00 sec) +-----------------------------------------------------------------------------------------------------+ | concat('After checking local variable is null the sum is = ',COALESCE(value1,0)+COALESCE(value2,0)) | +-----------------------------------------------------------------------------------------------------+ | After checking local variable is null the sum is = 0 | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.03 sec)
Advertisements