SQL CREATE PROCEDURE Keyword

 The CREATE PROCEDURE statement in SQL is used to define a stored procedure, which is a precompiled collection of one or more SQL statements that can be executed as a single unit. Stored procedures are commonly used to encapsulate repetitive tasks, enforce business logic, or improve performance by reducing network traffic.

Syntax:

CREATE PROCEDURE procedure_name
    [ (parameter1 datatype, parameter2 datatype, ...) ]
    [ AS ]
    BEGIN
        -- SQL statements
    END;

Key components:

• procedure_name: The name of the procedure you're creating.
• (parameter1 datatype, parameter2 datatype, ...): Optional parameters that can be passed to the procedure. Each parameter must specify a name and datatype.
• BEGIN ... END: The block of code that contains the SQL statements to be executed when the procedure is called.

Example:

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

In this example:

• The procedure is named GetEmployeeDetails.
• It accepts one input parameter, emp_id, which is an integer.
• The SQL statement inside the procedure fetches employee details for the given employee ID.

Types of Parameters:

1. IN: The parameter is used to pass a value into the procedure.
2. OUT: The parameter is used to return a value from the procedure.
3. INOUT: The parameter is used to both pass a value into the procedure and return a modified value.

Calling the Procedure:

Once the procedure is created, it can be executed (called) using the CALL statement:

CALL GetEmployeeDetails(101);

This will execute the stored procedure and return the details of the employee with employee_id 101.