SQL Stored Procedure

A stored procedure in Structured Query Language is a group of logical statements stored in the database for performing a particular task.

It is a subprogram consisting of a name, a list of parameters, and Transact-SQL statements.

Any user can store the stored procedure as a named object in the SQL database and can call it by using triggers, other procedures, and other programming applications such as Java, PHP, R, C#, Python, etc.

SQL database creates an execution plan and stores it in the cache memory when the stored procedure is called for the first time. The plan is reused by SQL Server, which executes the stored procedure quickly with reliable performance.

Types of Stored Procedure

Following are the two types of Stored Procedures in SQL:

User-defined Stored Procedures
System Stored Procedures

User-defined Stored Procedures

User-defined Stored Procedures are created by the database developers and administrators and stored in the current database.

This type of stored procedure provides one or more SQL statements for retrieving, updating, and deleting values from database tables.

User-Defined stored procedure is further categorized into the following two types:

T-SQL Stored Procedure
CLR Stored Procedure

T-SQL Stored Procedure

The Transact-SQL procedure accepts the parameters and returns them. This stored procedure manages INSERT, UPDATE, and DELETE statements with or without parameters and gives the row data in the output.

CLR Stored Procedure

CLR stored procedure is that stored procedure which is created by the combination of CLR (Common Language Runtime) and another stored procedure written in a CLR-based language like C# and VB.NET.

CLR procedures are the objects of .Net Framework, which execute in the memory of the SQL database server.

System Stored Procedures

SQL database server creates and executes the system stored procedures for administrative activities. SQL database server does not allow developers to interfere with system stored procedures.

Syntax of Stored Procedure in SQL

The following syntax is used to create the simple stored procedure in Structured Query Language:

CREATE PROCEDURE Procedure_Name
AS
/*    SQL Statements */
GO;

The following syntax is used to execute the stored procedure in Structured Query Language:

Example of Stored Procedure in SQL

Firstly, we have to create the table and insert the data into the table in SQL.

The following query creates the Student_Stored_Procedure table using the CREATE TABLE statement:

CREATE TABLE Student_Stored_Procedure
(
Student_ID INT NOT NULL, 
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT, 
Student_Marks INT
); 

The following SQL queries insert the record of students into the above table using INSERT INTO statement:

INSERT INTO Student_Stored_Procedure VALUES (101, Anuj, B.tech, 20, 88);
INSERT INTO Student_Stored_Procedure VALUES (102, Raman, MCA, 24, 98);
INSERT INTO Student_Stored_Procedure VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO Student_Stored_Procedure VALUES (107, Vikash, B.tech, 20, 78);
INSERT INTO Student_Stored_Procedure VALUES (111, Monu, MBA, 21, 65);
INSERT INTO Student_Stored_Procedure VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO Student_Stored_Procedure VALUES (121, Parul, BCA, 20, 97);
INSERT INTO Student_Stored_Procedure VALUES (123, Divya, B.tech, 21, 89);
INSERT INTO Student_Stored_Procedure VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO Student_Stored_Procedure VALUES (130, Nidhi, BBA, 20, 88);
INSERT INTO Student_Stored_Procedure VALUES (132, Priya, MBA, 22, 99);
INSERT INTO Student_Stored_Procedure VALUES (138, Mohit, MCA, 21, 92);

Let's see the record of the above table using the following SELECT statement:

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	99
138	Mohit	MCA	21	92

The following query creates the stored procedure which selects all the records from the above Student_Stored_Procedure table:

CREATE PROCEDURE Show_All_Students
AS
SELECT * FROM Student_Stored_Procedure
GO ;

Now, execute the stored procedure using the following query to see its output:

Output:

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	99
138	Mohit	MCA	21	92

Stored Procedure with One Parameter

The syntax for creating the stored procedure with one parameter is given below:

CREATE PROCEDURE Procedure_Name @Parameter_Name Datatype (size of the Parameter)
AS 
/*   SQL Statement * /
GO;

The syntax for executing the stored procedure with one parameter is given below:

The following query creates the stored procedure which shows the students of a particular course from the above table:

CREATE PROCEDURE Show_Particular_Course_Student @Student_Course nvarchar(50)
AS 
SELECT * FROM Student_Stored_Procedure WHERE Student_Course = @Student_Course
GO;

The following query executes the above-stored procedure and shows the record of B.tech students in the output:

Output:

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
107	Vikash	B.tech	20	78
114	Jones	B.tech	18	93
123	Divya	B.tech	21	89

Stored Procedure with Multiple Parameters

The syntax for creating the stored procedure with more than one parameter is given below:

CREATE PROCEDURE Procedure_Name @Parameter_Name_1 Datatype (size of the Parameter_1),  @Parameter_Name_2 Datatype (size of the Parameter_2), .....,  @Parameter_Name_N Datatype (size of the Parameter_N)
AS 
/*   SQL Statement * /
GO;

The syntax for executing the stored procedure with multiple parameters is given below:

EXEC Procedure_Name @Parameter_Name1 = Value, @Parameter_Name2 = Value, ....., @Parameter_NameN = Value;

The following query creates the stored procedure which shows the students of a particular course with a particular age from the above table:

CREATE PROCEDURE Show_Particular_Course_Age_Student @Student_Course nvarchar(50), @Student_AgeINT
AS 
SELECT * FROM Student_Stored_Procedure WHERE Student_Course = @Student_Course AND Student_Age =@Student_Age
GO;

The following query executes the stored procedure and shows the record of those students in the output whose course is B.tech and Age is 20:

EXEC Show_Particular_Course_Student @Student_Course = 'B.tech', @Student_Age = 20;

Advantages of Stored Procedures in SQL

Following are the important benefits or advantages of stored procedure in Structured Query Language:

Reduced Network Traffic: The stored procedure minimizes the network traffic between the application and the database server because instead of sending many lines of SQL code, the application only needs to pass the name and parameter of the stored procedure to the database server.
Stronger Security: The stored procedure provides high security because it restricts the users from accessing the data from the tables directly.
Reusable: Stored procedure can be used by multiple users without writing the same SQL code repeatedly. Re-usability of the code minimizes the development time.
Easy to Modify: Any user can easily modify the stored procedure with the help of ALTER TABLE command.
Increased Performance: Stored Procedure in SQL maximizes the performance of the application.

Next TopicSQL SELECT AVG

← prev next →