How To Create Procedure In SQL

Chapter: SQL Commands Last Updated: 24-03-2023 18:42:59 UTC

Program:

            /* ............... START ............... */
                --- Syntax of Proc

/*
CREATE PROCEDURE procedure_name
    [ @parameter1 datatype [ VARYING ] [ = default ] ]
    [ , @parameter2 datatype [ VARYING ] [ = default ] ]
    [ , ... ]
AS
BEGIN
    -- SQL statements to be executed
END;

*/

-- A Simple procedure

CREATE PROCEDURE SumTwoNumbers
    @num1 INT,
    @num2 INT
AS
BEGIN
    SELECT @num1 + @num2 AS Sum
END;



                /* ............... END ............... */
        

Output

EXEC SumTwoNumbers @num1 = 2, @num2 = 3;

Output would return the value 5.

Notes:

• A procedure in SQL is a group of SQL statements that are stored in the database and can be executed as a single unit. It's a named block of code that performs a specific task or set of tasks, and it can take input parameters and return output values.
• Procedures can be very useful because they allow you to encapsulate complex or frequently used SQL code into a single reusable unit. This can make your code more modular, easier to maintain, and less error-prone. Procedures can also improve performance by reducing the number of network round-trips between the database and client applications.
• To create a procedure, you define its name, input parameters (if any), and the SQL code that will be executed when the procedure is called. Once created, you can execute the procedure by calling its name and passing in any required input parameters.
• CREATE PROCEDURE is the keyword to create a new procedure.
• procedure_name is the name you want to give to your procedure.
• @parameter1, @parameter2, etc. are optional parameters you can define for your procedure. You can specify their data types and default values if needed.
• AS starts the procedure definition block.
• BEGIN and END delimit the SQL statements that make up the procedure's body.
• Overall, procedures are a powerful feature of SQL that can help you write cleaner, more efficient, and more maintainable code.

1