CREATE PROCEDURE

In SQL, a stored procedure is a precompiled collection of one or more SQL statements that perform a specific task. The CREATE PROCEDURE statement is used to define and create stored procedures in a database. Here’s an overview of the SQL CREATE PROCEDURE syntax and its key components:

Syntax

CREATE PROCEDURE procedure_name
    [parameter1 datatype1, parameter2 datatype2, ...]
AS
    -- SQL statements to define the procedure's logic
BEGIN
    -- SQL statements inside the procedure
END;

Let’s break down the components:

CREATE PROCEDURE: This is the beginning of the statement, indicating that you are creating a new stored procedure.

procedure_name: This is the name of the stored procedure. It must be a valid identifier and follow the naming rules of the database system you are using.

[parameter1 datatype1, parameter2 datatype2, …]: Optional parameters that can be passed to the stored procedure. Parameters are placeholders for values that are passed into the procedure when it is called. Each parameter is defined by a name and a data type.

AS: This keyword is used to separate the parameter list from the body of the stored procedure.

BEGIN…END: This is the block that contains the SQL statements that make up the logic of the stored procedure. All the statements between the BEGIN and END keywords belong to the stored procedure.

Example

Let’s look at a simple example:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

In this example, we create a stored procedure named GetEmployeeDetails that takes an @EmployeeID parameter and retrieves details for the specified employee from the Employees table.

To execute a stored procedure, you can use the EXECUTE or EXEC keyword:

EXEC GetEmployeeDetails @EmployeeID = 123;

This would call the GetEmployeeDetails stored procedure with the specified employee ID.

Stored procedures offer several advantages, including code reusability, improved performance, and better security. They are widely used in database systems to encapsulate and execute complex logic on the server side.