How To Create Procedure In SQL

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


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

CREATE PROCEDURE procedure_name
    [ @parameter1 datatype [ VARYING ] [ = default ] ]
    [ , @parameter2 datatype [ VARYING ] [ = default ] ]
    [ , ... ]
    -- SQL statements to be executed


-- A Simple procedure

    @num1 INT,
    @num2 INT
    SELECT @num1 + @num2 AS Sum

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


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

Output would return the value 5.


  • 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.
Similar Programs Chapter Last Updated
SQL Query To List All Databases SQL Commands 17-04-2023
SQL Query To Find Duplicate Records SQL Commands 17-04-2023
Group By In SQL SQL Commands 12-11-2020
Substring In SQL SQL Commands 27-11-2019
SQL SELECT Statement SQL Commands 16-03-2018