Stored Procedure

Introduction To Stored Procedure In SQL

Introduction:
In this article, you will learn what stored procedure is in SQL.

In SQL Server, the stored procedure is used for the re-usability of queries i.e. if we want to use the same query many times in our database, we will use the stored procedures. It is a collection of T-SQL (Transact-SQL) statement, which runs with a single execution.

• It executes the statement sequentially.

• It uses the parameters mapping concept.

• To return any value, we will then use the return statement.

• To use the data in the procedure, we will use the parameters.

• Stored procedures are used as a coding block in the database Server.

Types of Stored Procedure

SQL stored procedure is divided into three major parts:

System Store Procedure

A System Store Procedure is used to easily handle the many tasks and activities. If we do any updating or any changes in our database, we insert a backup or organization, functioning in the place of the modified database. These procedures are prefixed.

User Stored Procedure

A User Stored Procedure is used to store and compile the data in SQL. We know that the procedure is a collection of T-SQL (Transact-SQL) statement, which has done the work on .NET Framework CLR method.

Temporary Stored Procedure

This procedure works like a permanent procedure. Temporary Stored Procedure is stored in a temp db. There are two types of temporary procedures: local and global.

• Local Temporary Procedures shows only the present connection. If we will close our database, our connection is closed.

• Global Temporary Procedures are first generated and then shown to any user. They are deleted after the last session. These both Local and Global Temporary Procedures are totally different from each other.

We can also use user-defined functions and triggers in the stored procedures.

Advantages of Stored Procedure

SQL Server stored procedure has some advantages like:

• Stored procedure returns any- zero, single or multiple values.

• We can show any activity in SP.

• Stored procedure has both input and output parameters.

• SP is also used with XML for the clause.

• It uses DML statement with select.

• Stored procedure can test separately.

Disadvantages to Stored Procedures

The SQL Server stored procedure has some disadvantages like:

• If our data gives an error, stored procedure does not set up until the execution.

• We cannot store all the stored procedure in a single file, which means all the stored procedures are used separately.

Summary:
Thus, we learned that with the stored procedure, we used our query again and again in SQL.