EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial Stored Procedure in SQL 
 

Stored Procedure in SQL 

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated March 13, 2023

Stored Procedure in SQL 

 

 

Introduction to Stored Procedure in SQL

If a particular operation you want to perform over and over again then we use the stored procedure. A stored procedure is a SQL code which is saved and can be reused. Once the stored procedure we just need to call the procedure to make use of it. A stored procedure can be parameterized or a normal procedure. In this session, let us learn more about the stored procedure and how it is used and called for execution. Parameterized stored procedure requires the parameters to be passed to it so that the stored procedure will act based on the parameter value passed.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

Below is the syntax for the stored procedure:

create procedure<procedure_name>
as
/* - - - -  SQL statement - - - */
GO;

Below is the syntax to execute the stored procedure:

exec<procedure_name>;

Below is the syntax for the stored procedure with parameterized:

create procedure<procedure_name> @<parameter_name><datatype>
as
/* - - - -  SQL statement - - - */
GO;

Below is the syntax to execute the stored procedure with parameterized:

exec<procedure_name>@<parameter_name> = 'parameter_value';

How to use a stored procedure in SQL?

Step 1: Now let us consider and create a table and create a stored procedure to get the data from the table:

Code:

create table test_stored_procedure
(
library_id int,
No_of_vistors int,
library_pass varchar(10),
library_opentiming time,
library_closetiming time,
library_location varchar(20),
book_types varchar(40)
);

Step 2: Let us insert data into the table:

Code:

insert into test_stored_procedure values (1, 23,'Yes','9:00AM','6:00PM','Singasandra','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (2, 43,'Yes','9:00AM','6:00PM','Whitefield','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (3, 63,'Yes','9:00AM','6:00PM','Electonic city','Horrinsert into test_stored_procedure values (5, 13,'Yes','9:00AM','6:00PM','Singasandra','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (6, 43,'Yes','9:00AM','6:00PM','Whitefield','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (7, 33,'Yes','9:00AM','6:00PM','KR Puram','Horror/Fiction/Disney/Moral');or/Fiction/Disney/Moral');
insert into test_stored_procedure values (4, 33,'Yes','9:00AM','6:00PM','Singasandra','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (8, 13,'Yes','9:00AM','6:00PM','Singasandra','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (9, 53,'Yes','9:00AM','6:00PM','KR Puram','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (10, 63,'Yes','9:00AM','6:00PM','Singasandra','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (11, 73,'Yes','9:00AM','6:00PM','KR Puram','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (12, 83,'Yes','9:00AM','6:00PM','Electonic city','Horror/Fiction/Disney/Moral');
insert into test_stored_procedure values (13, 93,'Yes','9:00AM','6:00PM','Electonic city','Horror/Fiction/Disney/Moral');

Output:

Data

Step 3: Now let us create a stored procedure for the above table to get the data where “library_location” is ‘Singasandra’. Stored Procedure creation without parameters:

Code:

CREATE PROCEDURE library_stored_procedure
AS
SELECT * FROM TEST_STORED_PROCEDURE P WHERE P.LIBRARY_LOCATION ='Singasandra'
GO;

Output:

Stored Procedure creation

Step 4: Now let us execute the procedure:

Execute command of the stored procedure is below:

Code:

EXEC library_stored_procedure;

Output:

Stored Procedure in SQL 3

Step 5: Stored Procedure creation with parameters:

Code:

CREATE PROCEDURE library_stored_procedure_par @loc_name varchar(20)
AS
SELECT * FROM TEST_STORED_PROCEDURE P WHERE P.LIBRARY_LOCATION = @loc_name
GO;

Output:

Stored Procedure in SQL 4

Step 6: Execute command of the stored procedure is below:

Code:

EXEC library_stored_procedure_par loc_name='KR Puram';

Output:

Stored Procedure in SQL 6

Example to Implement Stored Procedure in SQL

Below is the example mentioned:

Example #1

Step 1: Now let us consider and create a table and create a stored procedure to get the data from the table:

Code:

create table paying_guest_pg
(
pg_id int,
pg_name varchar(20),
no_of_people int,
rent int
);

Step 2: Now let us insert data into the table:

Code:

insert into paying_guest_pg values (12 ,'Sri Laxshmi PG',100 ,6000);
insert into paying_guest_pg values (13 ,'Sai Ram PG',120 ,5400);
insert into paying_guest_pg values (14 ,'Sri Maha PG',100 ,6000);
insert into paying_guest_pg values (15 ,'Venkateshwara PG',200 ,5000);
insert into paying_guest_pg values (16 ,'Govinda PG',300 ,4500);
insert into paying_guest_pg values (17 ,'Zen stay PG',120 ,6000);
insert into paying_guest_pg values (18 ,'Radha PG',130 ,6000);
insert into paying_guest_pg values (19 ,'Krishna pg',140 ,6000);
Stored Procedure creation without parameters: -
CREATE PROCEDURE pg_procedure
AS
SELECT * FROM paying_guest_pg P
GO;

Output:

Insert data

Step 3: Execute command of the stored procedure is below:

Code:

EXEC pg_procedure;

Output:

command of Stored Procedure

Step 4: Stored Procedure creation with parameters:

Code:

CREATE PROCEDURE pg_procedure_par @rent int
AS
SELECT * FROM paying_guest_pg P where rent = @rent
GO;

Output:

creation with parameters

Step 5:

Code:

EXEC pg_procedure_par RENT=6000;

Output:

Stored Procedure in SQL 10

Conclusion

Things to remember in the above session are: If a particular operation you want to perform over and over again then we use the stored procedure. A stored procedure is a SQL code which is saved and can be reused. Once the stored procedure we just need to call the procedure to make use of it. A stored procedure can be parameterized or a normal procedure.

Recommended Articles

We hope that this EDUCBA information on “Stored Procedure in SQL ” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL TRIM()
  2. Cursor in MySQL
  3. PostgreSQL Procedures
  4. What is SQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW