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.

Stay updated with latest technology trends
Join DataFlair on Telegram!!

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:

SQL demo database

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:

single line comment in SQL

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:

block comment in SQL

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

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

Output:

procedure creation in SQL

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

CALL viewEmployees();

Output:

call procedure in SQL

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

SHOW PROCEDURE STATUS;

Output:

show procedure in SQL

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

DROP PROCEDURE GetAllProducts;

Output:

drop procedure in SQL

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.

Your 15 seconds will encourage us to work even harder
Please share your happy experience on Google | Facebook

4 Responses

  1. Amit Rawat says:

    How many DDL and DML queries we can write in one Stored Procedure ?

  2. Sujith says:

    Hi how to execute SP with multiple values for single parameter. In the above example suppose for city I want give two city names .

  3. Sujith says:

    Also while executing above procedure is not working. but when I put @ symbol before parameter then its working.

  4. Armeen says:

    please one suitable example of creating calling of stored producer

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.