Comments and Stored Procedure in SQL – Syntax and Example

In this tutorial, we will be focusing on the SQL stored procedures and comments. Let us now dive deep into the comments and stored procedures in SQL.

What are SQL Comments and the need for Comments in SQL?

In any programming language, when different developers work on the same code, communication of what the code does or why certain changes are being done is important.

To maintain effective communication and proper flow of code from stage zero to the production level we require accurate comments on each piece of code.

Some of the major uses of comments are as follows:

• The code becomes more readable.
• Transfer of code from one developer to another.
• Helps in maintaining the code and keeping it in check.

We have two types of comments available in SQL:

1. Single Line Comment in SQL
Syntax:

-- Single Line Comment

2. Block Comment in SQL
Syntax:

/* this block
is the part of 
Block comment */

What are SQL Procedures and their Needs?

We save redundant queries with short names so that they can be used anytime without the need of writing queries again and again.

Stored procedures in SQL are more like functions in high-level programming languages. The use of stored procedures helps in avoiding code redundancy and helps in enhancing the memory usage.

• To create a Procedure
  Syntax:

Delimiter //
CREATE PROCEDURE procedure name(parameters)
BEGIN
   statements;
END //
Delimiter;

• To call a Procedure
  Syntax:

CALL procedureName(arguments);

• To show all the Stored Procedures
  Syntax:

SHOW PROCEDURE STATUS;

• To drop a Procedure
  Syntax:

DROP PROCEDURE [IF EXISTS] procedureName;

Demo Database

Let us now have a look at our database DataFlair.
Query:

SELECT * FROM DataFlair;

Output:

Let us now dive deep into the topics using examples of both Stored Procedures and Comments.

Example 1: Let us now understand the single-line comment in SQL.
Query:

-- This query shows us the database.
-- The contents of DataFlair Database.
SELECT * FROM DataFlair;

Output:

Example 2: Let us now understand the block comments in SQL.
Query:

/* This query shows us 
the database.
The contents of DataFlair Database.*/
SELECT * FROM DataFlair;

Output:

Example 3: Let us now see how to create a procedure.
Query:

DELIMITER //
CREATE PROCEDURE viewEmployees()
BEGIN
    SELECT *  FROM dataflair;
END //
DELIMITER ;

Output:

Example 4: Let us now see how to call a Procedure.
Query: 

CALL viewEmployees();

Output:

Example 5: Let us now see the total created Procedures.
Query: 

SHOW PROCEDURE STATUS;

Output:

Example 6: Let us now see how to drop a Procedure.
Query: 

DROP PROCEDURE GetAllProducts;

Output:

Summary

In this tutorial, we have seen what are Stored Procedures and Comments in SQL.

We have taken into consideration all the types of Comments in SQL, i.e. Single Line and Block Comments. We have seen that comments are used to avoid confusion in code and increase the efficiency of the code.

Stored Procedure is like functions in high-level languages where they help to reuse the query, which is used frequently. Stored procedures help us to save our query and access it whenever required.

We have seen examples of all the comment types and the cycle of creating and dropping a Procedure. With various examples, we have also covered the creation, viewing, and deleting of a created procedure.