SQL Parameters in Procedures 🎯

Welcome to our SQL Parameters in Procedures tutorial! In this lesson, we'll learn how to create, call, and understand SQL procedures with parameters. This knowledge is crucial for writing efficient and reusable stored procedures in your database. Let's get started!

What are SQL Procedures? 📝

A procedure is a prepared SQL code block that you can execute multiple times with different arguments, also known as parameters. Procedures can contain a combination of SQL statements, such as SELECT, INSERT, UPDATE, and DELETE.

Why Use Procedures? 💡

Reusability: Procedures can be called multiple times with different parameters, making them more efficient than writing individual SQL statements.
Modularity: Procedures help break down complex tasks into smaller, manageable units.
Ease of Maintenance: Modifying a procedure affects all the places where the procedure is called, reducing the need for multiple code updates.

Creating a SQL Procedure with Parameters 💡

Let's create a simple SQL procedure that accepts two parameters and inserts them into a table.

sql

CREATE PROCEDURE InsertEmployee
(
    IN first_name VARCHAR(50),
    IN last_name VARCHAR(50),
    IN department_id INT
)
BEGIN
    INSERT INTO employees (first_name, last_name, department_id)
    VALUES (first_name, last_name, department_id);
END;

In the above example, we've defined a procedure named InsertEmployee that accepts three parameters: first_name, last_name, and department_id. The IN keyword before each parameter denotes an input parameter.

Calling a SQL Procedure with Parameters 💡

To call the InsertEmployee procedure, we use the CALL statement, followed by the procedure name and the parameters in parentheses.

sql

CALL InsertEmployee('John', 'Doe', 101);

In the above example, we're calling the InsertEmployee procedure with 'John' as the first name, 'Doe' as the last name, and 101 as the department id.

Using SQL Procedures in Real Projects 💡

Procedures are essential in managing complex database operations efficiently. Here's a practical example:

sql

CREATE PROCEDURE UpdateEmployeeSalary
(
    IN employee_id INT,
    IN new_salary DECIMAL(10,2)
)
BEGIN
    UPDATE employees SET salary = new_salary WHERE id = employee_id;
END;

In this example, we've created a UpdateEmployeeSalary procedure that updates the salary of an employee based on the provided employee id and new salary.

SQL Parameters in Procedures 🎯

SQL Parameters in Procedures 🎯

What are SQL Procedures? 📝

Why Use Procedures? 💡

Creating a SQL Procedure with Parameters 💡

Calling a SQL Procedure with Parameters 💡

Using SQL Procedures in Real Projects 💡

Quiz 💡