SQL: Procedures and Functions

In a nutshell, procedures and functions are sets of SQL statements that are executed as a unit to achieve a particular goal. Procedures are used to perform a specific task, while functions are used to calculate a value. But the real question is, why are they so powerful?

The Advantages of SQL Procedures and Functions

Firstly, SQL procedures and functions are pre-compiled and executed on the server side, which makes them much faster than running individual SQL statements. Additionally, since the logic and statements reside on the server, it reduces the client/server traffic, making your application more efficient.

Differences between SQL Procedures and Functions

Now, let’s dive into the differences between SQL procedures and functions.

1. Stored Functions must RETURN A VALUE while Procedures DON’T HAVE TO.
2. Stored Functions CANNOT USE SQL Statements that RETURN RESULT SET.
3. Stored Functions CANNOT USE SQL Statements that PERFORM TRANSACTION COMMITS OR CALLBACKS (DML such as INSERT/UPDATE/DELETE statements are not allowed).
4. Stored Functions are CALLED WITH ‘SELECT’ while stored Procedures are CALLED WITH ‘CALL’.
5. Stored Functions can be called from Procedure whereas Procedures CANNOT BE CALLED from Function.
6. EXCEPTION can be HANDLED by a try-catch block in a Procedure whereas a try-catch block cannot be used in a Function.

Creating SQL Procedures

To create a procedure in MySQL, you need to first change the delimiter. You can put any character instead of “$$” to signify the start and end of the procedure. Then, simply write the SQL statements that you want to execute inside the procedure, and close it with the end delimiter.

Here’s an example of a simple procedure:

DELIMITER $$
CREATE PROCEDURE name_of_procedure()
BEGIN
/*
SQL statements;
*/
END $$
DELIMITER ;

/*

Once you've created the procedure,
you can call it using the "CALL" keyword,
followed by the name of the procedure:
*/

CALL name_of_procedure(); 

Passing Parameters to SQL Procedures

You can also pass parameters to your SQL procedures using the “IN”, “OUT”, and “INOUT” keywords. Here’s an example:

DELIMITER $$
CREATE PROCEDURE name_of_procedure( IN in_name SMALLINT, OUT out_name VARCHAR(45))
BEGIN
/*
SQL statements;
*/
SELECT col_name INTO out_name FROM table_name WHERE col_id=in_name;

END $$
DELIMITER ;

To call the procedure, you’ll need to pass in the values for the parameters:

CALL name_of_procedure(3, @var_name); 

To Show

SELECT @var_name;

Here the value of @var_new will be the same as the output set in the procedure.

INOUT

In this case, a single variable will work as an input and an output parameter.

	
DELIMITER $$

CREATE PROCEDURE inOut_procedure(INOUT pas INT)
BEGIN
/*
SQL statements;
*/
SELECT Phoneno INTO pas FROM table_name
WHERE password=pas;
END

DELIMITER ;

To Call

SET @var_name=3;
CALL inOut_procedure(@var_name); 

To Show

SELECT @var_name;

To Show all the procedure which has a similar name

SHOW  PROCEDURE STATUS LIKE '%name_of_procedure%';
	

To Show all the procedures in the database

SHOW  PROCEDURE STATUS;

Creating SQL Functions

Creating a function is similar to creating a procedure but with a few key differences. Firstly, you need to specify the return type of the function. Secondly, you need to use the “RETURN” keyword to specify the value that the function will return. Here’s an example:

DELIMITER $$
CREATE Function name_of_function(input_param_name INT) RETURN INT
BEGIN
/*
SQL statements;
*/
RETURN some_int_value
END $$
DELIMITER ;

To call

SELECT name_of_function(@some_value);