MySQL Stored procedure interview Questions and Answer

Question: What is stored procedure?
A stored procedure is Structured Query Language (SQL) statements which has a name, a parameter list, and SQL statement(s) stored in MySQL Server.



Question: Give example of stored procedure?

DELIMITER $$
CREATE PROCEDURE getUsersAscending()
BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	ORDER BY first_name;    
END$$
DELIMITER ;

Question: Give example of stored procedure with parameter details?

DELIMITER $$
CREATE   PROCEDURE `getUsersDetails`(IdSearch INT)    
    BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	WHERE id=IdSearch;      

    END$$
DELIMITER ;

Question: To show all stored procedures:

SHOW PROCEDURE STATUS;

Question: How to show stored procedures in a specific database

SHOW PROCEDURE STATUS WHERE Db = 'db_name';

Question: MySQL stored procedures advantages?

1. Reduce network traffic
2. Make database more secure

Question: MySQL stored procedures dis-advantages?

1. Resource usages: If you use many stored procedures, the memory usage of every connection will increase substantially.
2. Troubleshooting:MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server
3. Maintenances Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess.

Question: How to call stored procedure with OR without arguments?
Call without argument

call getUsersAscending();

Call With argument

call getUsersDetails(10);

Question: How to delete the procedure?

DROP PROCEDURE IF EXISTS getUsersAscending;

Question: What if i am trying to delete the stored procedure for non-exist.?
It will give warning only, if you have used "IF EXISTS"
It will give Error, if you have used not used "IF EXISTS"


Question: How to Alter a stored procedure in mysql?
1: Delete the Stored procedure. 

2: Re-Create the Stored procedure.