SQL CREATE PROCEDURE Keyword

 The CREATE PROCEDURE keyword in SQL is used to define a stored procedure, which is a set of SQL statements that can be executed on demand. A stored procedure allows you to encapsulate logic and perform operations like data retrieval, updates, or other complex SQL queries in a reusable and efficient way.

Basic Syntax

CREATE PROCEDURE procedure_name
    [parameter1 datatype [IN | OUT | INOUT], 
     parameter2 datatype [IN | OUT | INOUT], 
     ...]
AS
BEGIN
    -- SQL statements to be executed
END;

Explanation:

• procedure_name: The name of the stored procedure.
• parameter1, parameter2, ...: These are optional input (IN), output (OUT), or input/output (INOUT) parameters that the procedure accepts. The parameter type can be any valid SQL datatype like INT, VARCHAR, DATE, etc.
• BEGIN ... END: The block where the SQL statements are written.

Example of a simple stored procedure:

CREATE PROCEDURE GetEmployeeDetails (IN employee_id INT)
BEGIN
    SELECT * FROM Employees
    WHERE EmployeeID = employee_id;
END;

• This procedure, named GetEmployeeDetails, accepts one input parameter employee_id and retrieves the details of the employee with that ID from the Employees table.

Example of a stored procedure with multiple parameters:

CREATE PROCEDURE AddEmployee (IN first_name VARCHAR(100), IN last_name VARCHAR(100), IN hire_date DATE)
BEGIN
    INSERT INTO Employees (FirstName, LastName, HireDate)
    VALUES (first_name, last_name, hire_date);
END;

• This procedure inserts a new employee into the Employees table with the FirstName, LastName, and HireDate values provided as input parameters.

Calling a Stored Procedure:

To execute a stored procedure after it's created, you use the CALL statement:

CALL GetEmployeeDetails(123);

• This will call the GetEmployeeDetails procedure and pass 123 as the employee_id parameter.

Notes:

• The IN parameter is the default, meaning it is used to pass values into the procedure.
• The OUT parameter is used to pass values from the procedure back to the calling environment.
• The INOUT parameter is used to both pass a value into the procedure and receive a value back from the procedure.
• The stored procedure can contain any valid SQL operations, including SELECT, INSERT, UPDATE, and DELETE.

Different database systems (e.g., MySQL, SQL Server, PostgreSQL, etc.) may have variations in syntax, but the general concept is the same across most platforms.