Stored Procedure in SQL: Definition, Usage, & Implementation

In this blog, we will understand stored procedures in SQL, their usage, and how we create a stored procedure in SQL. We will look into different types of stored procedures in SQL and understand the temporary stored procedures in SQL.

Table of Contents

• What is a Stored Procedure in SQL?
• Benefits of Using Stored Procedures in SQL
• How to Create a Stored Procedure in SQL
• Encryption of Stored Procedures in SQL
• How to Create a Temporary Stored Procedure
• How to Show the Output of a Stored Procedure
• Using DDL Commands in Stored Procedure
• Conclusion

Watch this SQL course video to learn more about its concepts:

What is a Stored Procedure in SQL?

A stored procedure is a predefined set of instructions that are grouped to perform specific tasks or operations within a database. It is stored in the database and can be used multiple times with different parameters as per the user’s requirements. This helps in the modularization of code and enhances security, thus improving performance by reducing the need to send multiple queries to the database server.

Enroll in this professional Data Analyst Course to learn more about data analytics from experts.

Benefits of Using Stored Procedures in SQL

Stored procedures in SQL are used for several reasons, Here are some benefits of using stored procedures in SQL as discussed below in detail:

• Performance Enhancement: Stored procedures optimize the data performance by reducing network traffic and enhancing the execution plan. They store the pre-compiled instructions, which restrict repetitive tasks and thus improve the response time.
• Security Measures: Stored procedures offer a layer of security by restricting direct access to sensitive data. Users interact with predefined procedures, which ensure controlled data access and prevent unauthorized actions in the database.
• Code Reusability: It creates a reusable block of code instead of rewriting the same code for similar tasks. We can call a stored procedure whenever needed. 
• Simplify Database Management: By compiling frequently used operations into single units, stored procedures in SQL simplify complex database tasks. thus making database management more structured and manageable.

How to Create a Stored Procedure in SQL

There are various conditions to create stored procedures in SQL. Mainly, it includes without parameters, with parameters, and default parameters.

Let us discuss each type in detail:

Stored Procedure without Parameter

We can create a simple stored procedure without using the parameters in an easy way.

Syntax:

Create procedure procedure_name
As
Variables; 
Begin
//Statements to be executed
End;

Example: To demonstrate the implementation of stored procedures in SQL, we will create two tables, which we will use throughout the blog. 

CREATE TABLE Students(
Student_ID INT,
StudentName VARCHAR(100)
);
INSERT INTO Students VALUES (1, Mark');
INSERT INTO Students VALUES (2, 'John');
INSERT INTO Students VALUES (3, 'Nobita');
INSERT INTO Students VALUES (4, 'Alpha');
INSERT INTO Students VALUES (5, 'Gama');
SELECT * FROM Students;

Output:

Now we will create another table

CREATE TABLE StudentRemarks(
Student_ID INT,
StudentRemarks VARCHAR(800)
);
INSERT INTO StudentRemarks VALUES (1, 'Good');
INSERT INTO StudentRemarks VALUES (2, 'Average');
INSERT INTO StudentRemarks VALUES (3, 'Excellent');
INSERT INTO StudentRemarks VALUES (4, 'Good');
INSERT INTO StudentRemarks VALUES (5, 'Outstanding');
SELECT * FROM StudentRemarks;

Output: 

We have created both tables. Now, we will start creating the stored procedure in SQL with the syntax mentioned earlier. For the simple procedure, we will have to use the JOIN keyword to join both tables and output a new one with Student_ID, StudentName, and StudentRemarks.

CREATE PROCEDURE intellipaat
AS
BEGIN
SET NOCOUNT ON
SELECT S.Student_ID, S.StudentName, SD.StudentRemarks  FROM 
Students S
INNER JOIN StudentRemarks SD ON S.Student_ID=SD.Student_ID
END

Output: 

We have created a simple stored procedure, and the commands were executed successfully.

Let us understand how a stored procedure is executed in SQL: 

Executing a Stored Procedure in SQL

To execute a stored procedure in SQL, we follow the syntax given below.

Syntax: 

EXEC procedure_name 

Example: 

EXEC intellipaat

Output: 

Stored Procedure with Parameter

To create a stored procedure in SQL with parameters, we can use the CREATE PROCEDURE statement and define parameters within parentheses after the procedure name.

Syntax:

Create procedure procedure_name
@parameter1 datatype
@parameter2 datatype 
…..
AS 
BEGIN
//statement to execute
END

Now, let’s look at an example that demonstrates how to create a stored procedure with parameters.

CREATE PROCEDURE intellipaat
(@S_Id INT)
AS
BEGIN
SET NOCOUNT ON
SELECT S.Student_ID, S.StudentName, SD.StudentRemarks  FROM 
Students S
INNER JOIN StudentRemarks SD ON S.Student_ID=SD.Student_ID
WHERE S.Student_ID = @S_Id
END

Execution:

EXEC intellipaat 3

Output:

Stored Procedure with Default Parameter

To create a stored procedure with default parameters in SQL, you can specify default values for parameters within the CREATE PROCEDURE statement. 

Syntax:

Create procedure procedure_name
@parameter1 datatype = default_value
@parameter2 datatype = default_value 
…..
AS 
BEGIN
//statement to execute
END

Example:

CREATE PROCEDURE intellipaat
(@S_Id INT = 2)
AS
BEGIN
SET NOCOUNT ON
SELECT S.Student_ID, S.StudentName, SD.StudentRemarks  FROM 
Students S
INNER JOIN StudentRemarks SD ON S.Student_ID=SD.Student_ID
WHERE S.Student_ID = @S_Id
END

Execution:

EXEC intellipaat

Output:

Encryption of Stored Procedures in SQL

The encryption of stored procedures in SQL involves hiding the code logic within the procedure by converting it into an encrypted format. This encryption ensures that the code of a stored procedure is not easily readable to unauthorized users or database administrators.

Syntax:

Create procedure procedure_name
WITH ENCRYPTION
AS 
BEGIN
//statement to execute
END

Example:

CREATE PROCEDURE dbo.usp_GetCatsByName @catname varchar(70)
WITH ENCRYPTION
AS
SELECT 
    CatId,
    CatName,
    Phone
FROM dbo.Cats
WHERE CatName = @catname;
GO

Execution:

EXEC sp_helptext 'usp_GetCatsByName';

Output:

How to Create a Temporary Stored Procedure

The stored procedures that are created temporarily in the database are defined as temporary stored procedures. They are not stored permanently in the database.

There are two types of temporary stored procedures:

1. Local temporary stored procedures
2. Global temporary stored procedures

Local Temporary Stored Procedure

For creating a local temporary procedure, you need to use the # prefix before the procedure name. These procedures are created on a local connection and are only accessible on that connection. Once the connection is closed, the procedure is dropped automatically. 

Example:

CREATE PROCEDURE #Temporary_procedure
AS
BEGIN
PRINT 'I love intellipaat'
END

Executing the procedure:

EXEC #Temporary_procedure

Global Temporary Stored Procedure

For creating a local temporary procedure, you need to use the ## prefix before the procedure name. These procedures can be accessed by other connections in the database. Once the connection is closed the procedure is dropped automatically. 

Syntax:

CREATE PROCEDURE ##procedure_name
AS
BEGIN
//statement to execute
END

Example:

CREATE PROCEDURE ##intellipaat
AS
BEGIN
PRINT 'welcome to intellipaat'
END
Executing the procedure:
EXEC ##intellipaat;

Prepare yourself for the industry by going through these SQL Interview Questions and Answers!

How to Show the Output of a Stored Procedure

A stored procedure in SQL that has an output parameter will return a value after the execution. We can create a stored procedure with an output parameter that shows the results as per the user’s requirements.

Example: Suppose we have an employee table as shown below, and we want to fetch the data for a particular user. For that, we can use an output parameter in the stored procedure.

Code:

Output: 

Using DDL Commands in Stored Procedure

We can use DDL commands in stored procedures to modify or rename a stored procedure in SQL.

Let’s understand each of these DDL commands in detail:

Modify

To modify a stored procedure in SQL, we use the ALTER command in the same way as we do to alter a table.

Syntax: 

ALTER procedure procedure_name
As
Variables; 
Begin
//Statements to be executed
End;

Example:

ALTER PROCEDURE intellipaat
AS
BEGIN
SET NOCOUNT ON
SELECT S.Student_ID, S.StudentName, SD.StudentRemarks  FROM 
Students S
INNER JOIN StudentRemarks SD ON S.Student_ID=SD.Student_ID
END

Output: 

Rename

In SQL, we cannot rename a stored command by using rename. To rename a stored procedure in SQL, we use the built-in function named sp_rename.

Syntax:

sp_rename ‘old_name’, ‘new_name’

Using the above syntax, we will rename the Students stored procedure that we used in the previous section.

sp_rename 'Students', 'Students_data';

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

EXEC Students_data;

Conclusion

Stored procedures in SQL offer performance boosts, security layers, and code reusability. They streamline complex tasks, simplify management, and enhance database security. Stored procedures can be defined with or without parameters, even with default values. Encryption hides code logic, while temporary procedures offer local or global access. DDL commands like modify or rename help in managing procedures effectively.

We tried our best to make you clear about the topic, but if you are still left with any doubts, reach out to us on our Community Page!