How to Create Stored Procedures in SQL?

Procedures are the subprograms which can be created and saved in the database as database objects. Just as you can in other languages, you can create and drop procedures in SQL as well. In this article, let’s explore procedures in SQL with syntax and examples.

The topics discussed in the article are:

• What is a procedure in SQL?
• SQL Procedure Syntax
• Example Procedure in SQL
• Advantages of SQL Procedures

What is a procedure in SQL?

A procedure in SQL (often referred to as stored procedure), is a reusable unit that encapsulates the specific business logic of the application. A SQL procedure is a  group of SQL statements and logic, compiled and stored together to perform a specific task.

Listed below are key features of the SQL procedures:

• Easy to implement because they use a very simple high-level, strongly-typed language
• Supports three types of parameters, namely, input, output, and input-output parameters.
• More reliable than equivalent external procedures.
• SQL procedures promote reusability and maintainability.
• Supports a simple, but powerful condition and error-handling model.
• Return a status value to a calling procedure or batch to indicate success or failure and the reason for failure.

Now that you know what procedures are and why they are needed, let’s discuss the syntax and example of procedure in SQL.

Syntax of procedures in SQL

The following illustrates the basic syntax of creating a procedure in SQL:

CREATE [ OR REPLACE] PROCEDURE procedure_name [
(parameter_name [IN | OUT | IN OUT]  type [ ])]
{IS | AS }
BEGIN [declaration_section]
executable_section 
//SQL statement used in the stored procedure
END
GO

Syntax Terminologies

Parameter

A parameter is a variable that holds a value of any valid SQL datatype through which the subprogram can exchange the values with the main code. In other words, parameters are used to pass values to the procedure. There are 3 different types of parameters, which are as follows:

• IN: This is the Default Parameter, which always receives the values from the calling program. It is a read-only variable inside the subprograms and its value cannot be changed inside the subprogram.
• OUT: It is used for getting output from the subprograms.
• IN OUT: This parameter is used for both giving input and for getting output from the subprograms.

Other Terminologies

• procedure-name specifies the name of the procedure. It should be unique.
• [OR REPLACE] option allows the modification of an existing procedure.
• IS | AS Clause, they set the context to execute the stored procedure.  The difference is, the keyword ‘IS’ is used when the procedure is nested into some other blocks and if the procedure is standalone then ‘AS’ is used.
• Code_Block declares the procedural statements that handle all processing within the stored procedure. The content of the code_block depends on the rules and procedural language used by the database.

Master the art of database querying and data retrieval with our SQL Certification Course.

Find out our MS SQL Course in Top Cities

Procedure in SQL: Examples

Example1

The following example creates a simple procedure that displays the welcome message on the screen when executed. Then, the procedure will be:

CREATE OR REPLACE PROCEDURE welcome_msg
(para1_name IN VARCHAR2)
IS 
BEGIN 
    dbms_output.put_line (‘Hello World! '|| para1_name);
END; 
/

Execute the stored procedure. A standalone procedure can be called in two ways −

• Using the EXECUTE keyword
• Calling the name of the procedure from a SQL block

The above procedure can be called using Execute keyword as follows:

 EXEC welcome_msg (‘Welcome to Edureka!’);

Output

Hello World! Welcome to Edureka 

Procedure is executed, and the message is printed out as “Hello World! Welcome to Edureka”.

Example2

Let’s suppose that you have a table with Emplyoee details, like, EmployeId, Firstname, Lastname, and DepartmentDetails.

This example creates a SQL procedure that will return an employee name when the EmployeId is given as the input parameter to the stored procedure. Then, the procedure will be:

Create  PROCEDURE GetStudentName 
(
@employeeID INT,                       --Input parameter ,  employeID of the employee
@employeName VARCHAR(50)  OUT  --Output parameter, employeeName of employee
AS
BEGIN
SELECT @employeName= Firstname+' '+Lastname FROM Employee_Table WHERE EmployeId=@employeID
END

Steps to execute:

• Declare @employeName as nvarchar(50)
• EXEC GetStudentName 01, @employeName output
• select @employeName

The above procedure on giving employee id as input returns the name of that particular employee. Suppose if we have an output parameter then we first need to declare the variable to collect the output values.  Now let’s check out the advantages of procedure in SQL.

Advantages of procedures in SQL 

The main purpose of stored procedures in SQL is to hide direct SQL queries from the code and improve the performance of database operations such as select, update, and delete data. Other advantages of procedure in SQL are:

• Reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less.
• Enables the reusability of code 
• Enhances the security since you can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
• Support nested procedure calls to other SQL procedures or procedures implemented in other languages.

To conclude, procedures in SQL (stored procedures) not only enhance the possibility of reusing the code but also increases the performance of the database. How? By reducing the traffic of the network by reducing the amount of information sent over the network. With this, we have come to the end of this article. 

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this ‘Procedures in SQL; article and we will get back to you.