Updated on 06th Dec, 23 9.1K Views

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

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 Analytics 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.

Get 100% Hike!

Master Most in Demand Skills Now !

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:

Stored Procedure without Parameter

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: 

Stored Procedure without Parameter

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: 

Stored Procedure without Parameter

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: 

Executing a Stored Procedure in SQL

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
Stored Procedure with Parameter

Execution:

EXEC intellipaat 3

Output:

Stored Procedure with Parameter

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
Stored Procedure with Default Parameter

Execution:

EXEC intellipaat

Output:

Stored Procedure with Default Parameter

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:

Encryption of Stored Procedures in SQL

Career Transition

Intellipaat Job Guarantee Review | Intellipaat Job Assistance Review | Data Engineer Course
Got Job Promotion After Completing Artificial Intelligence Course - Intellipaat Review | Gaurav
How Can A Non Technical Person Become Data Scientist | Intellipaat Review - Melvin
Artificial Intelligence Course | Career Transition to Machine Learning Engineer - Intellipaat Review
Non Tech to Data Scientist Career Transition | Data Science Course Review - Intellipaat

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!

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.

How to Show the Output of a Stored Procedure

Code:

How to Show the Output of a Stored Procedure

Output: 

How to Show the Output of a Stored Procedure

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: 

Modify

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!

Course Schedule

Name Date Details
Data Scientist Course 02 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 09 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 16 Mar 2024(Sat-Sun) Weekend Batch
View Details

Leave a Reply

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

Speak to our course Advisor Now !

Related Articles

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.