Create A Stored Procedure In SQL

The CREATE PROCEDURE statement in SQL can be used to construct a stored procedure. The fundamental syntax is as follows:

CREATE PROCEDURE procedure_name
    @parameter1 data_type,
    @parameter2 data_type,
    ...
AS
BEGIN
    -- SQL statements to be executed
END

Let’s look at the syntax:

CREATE PROCEDURE: This statement is used to create a new stored procedure.

Procedure name:  Enter the name of the saved procedure you wish to create here.
@parameter1, @parameter2, @parameter3,…:  These are optional parameters that can be passed to the stored process. You may define as many parameters as you need, as well as the data type for each one.

AS:  This term marks the beginning of the stored procedure specification.

BEGINNING AND ENDING:  When the stored procedure is invoked, these keywords encapsulate the SQL statements that will be executed.

Here’s a basic stored procedure that takes two parameters and returns the sum:

CREATE PROCEDURE sp_add_numbers
    @num1 INT,
    @num2 INT
AS
BEGIN
    SELECT @num1 + @num2 AS 'sum'
END

To execute the stored procedure, you can use the EXECUTE statement, like this:

EXECUTE sp_add_numbers 2, 3

This will return the result set with the sum of 2 and 3.