Stored procedures and functions are database objects in SQL that allow you to encapsulate a set of SQL statements into a reusable and callable unit. They offer several advantages, including code reusability, security, and performance optimization. While both stored procedures and functions provide similar functionality, there are some differences in their usage and capabilities:

Stored Procedures: A stored procedure is a collection of SQL statements that can be executed as a single unit. It can accept input parameters, perform operations, and return output values. Stored procedures are often used to encapsulate business logic, data manipulation, and complex operations within the database.

Advantages of Stored Procedures:

• Modularization: You can organize and encapsulate complex logic within a stored procedure, promoting code reusability and maintainability.
• Performance: Stored procedures can be precompiled and cached, resulting in faster execution compared to ad-hoc queries.
• Security: Stored procedures can limit direct table access and provide controlled access to data through parameterized inputs.
• Reduced Network Traffic: By executing code on the database server, you reduce the amount of data transferred between the server and the client.

Example of a Stored Procedure:

CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)
BEGIN
SELECT first_name, last_name, salary
FROM employees
WHERE employee_id = emp_id;
END;

Example of the procedure:

DELIMITER //
CREATE PROCEDURE get_user_details (IN id INT)
BEGIN
SELECT users.id, users.username, users.email, users.signup_type
FROM users
WHERE users.id = id;
END;
//
DELIMITER ;

CALLING THE PROCEDURE:-

CALL get_user_details(35)

Functions: Functions are similar to stored procedures but are typically used to compute and return values rather than perform actions. Functions can be used in SQL queries to calculate values based on input parameters and return the result. Functions can be categorized into scalar functions (returning a single value) and table-valued functions (returning a table).

Advantages of Functions:

• Code Reusability: Functions allow you to encapsulate frequently used calculations, making them easily reusable in queries.
• Consistency: Since functions return values based on their inputs, they ensure consistent results across queries.
• Modularity: Complex calculations can be abstracted into functions, simplifying queries and improving readability.

Example of a Scalar Function:

CREATE FUNCTION CalculateTax(IN salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
RETURN salary * 0.1; -- Assume 10% tax rate
END;

In summary, both stored procedures and functions are essential tools for encapsulating and managing SQL logic. Stored procedures are suitable for performing actions and encapsulating business logic, while functions are more focused on returning calculated values. Choose the appropriate option based on your specific needs, such as code reusability, performance optimization, and data manipulation requirements.