Stored Procedure in SQL: Benefits And How to Create It

Published On: 22 March 2023.By .
  • Digital Engineering

What is a procedure ?

A stored procedure in SQL is a group of SQL statement that are stored together in a database. Based on the statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the database, and return value, if any. Thus, it allows you to pass the same statements multiple times, thereby, enabling reusability.

Difference Between Store Procedures  & Triggers:

Working Flow of Store Procedure:

Syntax to create a procedure:

It has a name, parameter list and sql statement.

The CREATE PROCEDURE statement is used to create the Stored Procedure, ans the AS keyword is used to indicate the start of the procedure’s logic. The SQL Statement that make up the procedure’s logic are placed between the BEGIN and END keywords.

Call Procedure:

To call a procedure we use the call Keyword and then procedure name and paranthesis.

Eg:-

Parameters:

There may be times where you want to pass information to the stored procedures

1. Create Procedure p()

2. Create Procedure p( [IN] name data-type)

3. Create procedure p( OUT name data-type)

4. Create Procedure p (INOUT name data -type)

Type of Parameters:

IN: It is the default parameter that will receive input value from the program

OUT: It will send output value to the program , the value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program.

IN OUT: It is the combination of both IN and OUT. Thus, it receives from, as well as sends a value to the program

IN Eg:

OUT Eg:

INOUT Eg:

Set @y=5

Loops & Statement:

IF-THEN- ELSE

Case:

While —–END WHILE:

Variables:

A variable is a name that refers to a value and name that represents a value stored in the computer memory

1. The statements used to define variables

2. Eg: declare a int;

3. variable are declared between the begin and end tag.

4. Scope of variable:

5. Call Scope example : call p ()

Example : Create Store Procedure for insert data in student and student fee table if Student_Id take as variable.

Create Store Procedure:

Call Store Procedure:

Output:

Stu_fee table:

How to Modify a Stored Procedure in SQL?

You can modify a stored procedure just like modifying a table in the database using the ALTER command.

Output:

As you can see, the output shows “Command(s) completed successfully.” Thus, the GetCarDesc procedure that you created earlier is now modified.

How to Rename a Stored Procedure in SQL?

You can rename a stored procedure in SQL using the in-built procedure sp_rename. The syntax for renaming is:

sp_rename ‘old_name’, ‘new_name’

Using the above syntax, we will rename the GetCarDesc stored procedure that we have altered in the above section.

You can confirm the change by executing the stored procedure with the new name.

Output:

As you can see in the output, it executes the stored procedure successfully, confirming the rename.

SQL Stored Procedures can be executed in a variety of ways, including:

  1. Executing the Stored Procedure from within a SQL script using the EXEC statement.
  2. Calling the Stored Procedure from within an application using an API or library
  3. Scheduling the Stored Procedure to run at a specific time using a SQL Server Agent job.
  4. Creating a trigger that automatically executes the Stored Procedure when a specific event occurs.

Advantages:

  1. Stored procedure increases performance of application.
  2. Stored procedure is secured.
  3. Stored procedure is reusable.
  4. Stored procedure is transparent to any application which wants to use it.
  5. Stored Procedure reduced the traffic between application and database.
  6. Easy to modify with the help of the ALTER TABLE command.

Disadvantages:

  1. Take Memory and CPU usage more.
  2. Difficult to maintain
  3. Difficult to debug.

Conclusion:

SQL Stored Procedures are a powerful feature of any database management system. Help to improve reduce code duplicate, security and efficiency. They provide a way to encapsulating complex SQL queries and logic into a single, reusable entity that can be executed from within the database. Creating and using Stored Procedures is a straightforward process, making them an essential tool for any database administrator.

Related content

That’s all for this blog